Problem with Join (part 2) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Problem with Join (part 2)

Author

Topic

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-04-15 : 07:29:54

I made a part 2 because it would clear things up I guess.

Right, so I have this SP with

....
Select
ZMZKOrder zk
right join ZMProduct pr on (zk.zkproductid = pr.productid)
...

AND we have

...
Where
((zk.zkmoduleid = @moduleid) or (zk.zkmoduleid is Null))
....

Actually what I want is:

If there really is a corresponding productid (because of the right join, this doesn't have to be true) it must check that this zk.zkmoduleid = @moduleid is true.

And if there is NO corresponing productid, it must check that zk.zkmoduleid is Null is true.

And not both at the same time.

Does anyone have any ideas please?

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-04-15 : 08:13:36

Am I not explaining it right?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-15 : 08:16:51

[code]SELECT *
FROM ZMProduct AS pr
LEFT JOIN ZMZKOrder AS zk ON zk.zkproductid = pr.productid
AND zk.zkmoduleid = @moduleid[/code]

E 12°55'05.25"
N 56°04'39.16"

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-04-15 : 08:24:38

Nice, But this will not include the:

And if there is NO corresponing productid, it must check that zk.zkmoduleid is Null is true.

You see, it must return all rows, no matter if there is a corresponding row in the ZMZKorder table.
If there is a corresponding row (given zk.zkproductid = pr.productid) and (zk.zkmoduleid = @moduleid) then it will return some values for the zk.* fields.

If there is NO corresponding row (so zk.zkproductid <> pr.productid
it should return NULL values for the zk.* fields.

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-04-15 : 09:43:28

Nobody has any idea, please??

The secret to creativity is knowing how to hide your sources. (Einstein)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-15 : 11:56:16

You have to make your requirement clear with some sample data if you really wish to get an accurate solution. It seems like you are not clear with what you're asking.

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-04-15 : 12:20:30

Ok, I'll try, but it's a complicated thing with multiple tables.
so I'll try to give a simple example

the products table is filled with products

Productid productname etc.
123 name 1
234 name 2
789 name 3
654 name 4

The ZMZKorder table initially is not filled with anything but lets say the content is like this:

ZKorderid ZKModuleId ZKProductID ZKorder ZKZichtbaar
1 9999 234 15 true
2 9999 789 16 true

Now when I call the SP for @moduleid = 4444 then it should always return all rows wheter there is a matching record in the ZKorderID table or not. So for example it will return.

ZKorderid ZkModuleID ZkProductID ZKorder ZkZichtbaar ProductID Prodnm

Null Null Null Null Null 123 name 1

If I call this for @moduleId = 9999 it should return
ZKorderid ZkModuleID ZkProductID ZKorder ZkZichtbaar ProductID Prodnm

1 9999 234 15 true 234 name 2
2 9999 789 16 true 789 name 3
Null Null Null Null Null 654 name 4

However, there is a posibility to add a product from another module to your own module. This is done by the subquery.

So module 7777 might also call the product with id=789
So if you do @ModuleID = 7777 it still should be able to return

ZKorderid ZkModuleID ZkProductID ZKorder ZkZichtbaar ProductID Prodnm

Null Null Null Null Null 789 name 3

So in this case ZkProductID <> ProductID (Null <> 789) and ZKmoduleID is Null (while @ZKModuleID = 7777)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-15 : 12:46:36

Still not quite clear. In your first condition why wont you get this?
Null Null Null Null Null 654 name 4

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-04-15 : 14:13:58

I actually am trying to modify an existing procedure.
So the sp has a lot more parameters, like @netwerk.
So in the first statement let's say @netwerk = 18 which will only return productid 123
In the second statement let's say @netwerk = 16 which will return 234, 789 and 654
In the third statement let's say @netwerk = 22 which will actually returns nothing, but via a subquery calls product 789 which actually belongs to network 18

I told you the sp is actually more complicated...

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-04-15 : 14:17:08

It does:

...
from
ZMProduct pr
left join ZMZKOrder zk on (zk.zkproductid = pr.productid)
....

and

...
where
((zk.zkmoduleid = @moduleid) or (zk.zkmoduleid is null))
...

...
and ((lo.LocatiehoofdNetwerkid = @NetwerkID) or EXISTS (select * from ZMNetwerkPZProduct Where ZMNetwerkPZProduct.ProductID = pr.ProductID AND ZMNetwerkPZProduct.NetwerkPZID = @netwerkid))
...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-15 : 14:23:47

try this too:-

Select fields
from (select * from ZMZKOrder where zkmoduleid = @moduleid) zk
right join ZMProduct pr
on zk.zkproductid = pr.productid
...

AND we have

...
Where
other conditions
....

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-04-15 : 14:41:47

You know what....

Your an SQL god!!!!!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-04-15 : 15:37:18

If Visakh's solution works why doesn't Peso's?

As Far as I can tell they are the exact same query(they produce the same results); one using a left outer join with join conditions and the other a derived table with a right outer join.

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-04-15 : 15:43:31

I don't think they're exatcly the same....

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-04-15 : 16:31:58

Actually, they are exactly the same. (Meaning they do exactlyt he same thing, in a different way. :))

Here is some code that uses both queries. I thought this might help illustrate as we are all learning. I re-wrote Visakh's query so that the order is similar to Peso's to help show how they are similar:

DECLARE @Product TABLE (ProductID INT, ProductName VARCHAR(50))
INSERT @Product
SELECT 123, 'name' 
UNION ALL SELECT 234, 'name'  
UNION ALL SELECT 789, 'name' 
UNION ALL SELECT 654, 'name' 

DECLARE @Order TABLE(ZKorderid INT, ZKModuleId INT, ZKProductID INT, ZKorder INT, ZKZichtbaar BIT)

INSERT @Order
SELECT 1, 9999, 234, 15, 1
UNION ALL SELECT 2, 9999, 789, 16, 1

DECLARE @ModuleID INT
SET @ModuleID = 9999

SELECT 
	*
FROM 
	@Product AS P
LEFT OUTER JOIN
	@Order AS O
	ON P.ProductID = O.ZKProductID
	AND O.ZKModuleId = @ModuleID

-- Original
Select *
from (select * from @Order where zkmoduleid = @moduleid) zk 
right join @Product pr
on zk.zkproductid = pr.productid

-- Reordered
SELECT
	*
FROM 
	@Product pr
LEFT OUTER JOIN
	(
		SELECT * 
		FROM @Order 
		WHERE zkmoduleid = @moduleid
	) AS zk 
	ON zk.zkproductid = pr.productid

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-04-16 : 13:59:54

I still think it is different, the total SP looks like this now:

ALTER procedure [dbo].[ZorgKiezenTweeList]

@ModuleID int,
@CSVInstellingID varchar(1000) = '',
@LocatieNaam varchar(25) = '',
@PlaatsNaam varchar(25) = '',
@NetwerkID int = Null,
@InstellingSoortCatWeergave int = Null
as

begin
set nocount on
-- voor als er geen netwerkid wordt meegegeven, geef alles terug
IF @NetwerkID = -1
BEGIN
select
zk.zkorderid as zkorderid,
zk.zkportalid as portalid,
zk.zkmoduleid as moduleid,
zk.zkorder as [order],
zk.zkzichtbaar as zichtbaar,
pr.ProductID as ProductID,
pd.ProductDetailsAantalPlaatsen as AantalPlaatsen,
pd.ProductDetailsAantalPlaatsenVrij as PlaatsenVrij,
pd.ProductDetailsAantalPlaatsenVrijWeergeven as AantalPlaatsenVrijWeergeven,
pd.ProductDetailsAantalPlaatsenVrijLaatstbijgewerkt as PlaatsenVrijLaatstbijgewerkt,
st.InstellingSoortID as InstellingSoortID,
st.InstellingSoortNaam as InstellingSoortNaam,
st.InstellingSoortSubNaam as InstellingSoortSubNaam,
bi.BasisIndelingNaam as BasisIndelingNaam,
lo.LocatieID as LocatieID,
lo.LocatieNaam as LocatieNaam,
lo.LocatieBezoekPlaatsnaam as LocatieBezoekPlaatsnaam,
lo.LocatieTelefoon as LocatieTelefoon,
lo.LocatieHoofdProvincieID as LocatieHoofdProvincieID,
lo.LocatiehoofdNetwerkid as LocatieHoofdNetwerkid,
i.InstellingID as InstellingID,
i.InstellingNaam as InstellingNaam
from
(select * from ZMZKOrder where Zkmoduleid = @moduleid) zk
right join ZMproduct pr on (zk.zkproductid = pr.productid)
inner join ZMInstellingSoort st on (pr.ProductInstellingSoortID = st.InstellingSoortID)
inner join ZMBasisIndeling bi on (pr.ProductBasisIndelingID = bi.BasisIndelingID)
inner join ZMLocatie lo on (pr.ProductLocatieID = lo.LocatieID)
left join ZMProductDetails pd on (pr.ProductProductDetailsID = pd.ProductDetailsID)
inner join ZMInstelling i on (lo.LocatieInstellingID = i.InstellingID)
where
pr.ProductPublicatieStatus = 1
and ((@InstellingSoortCatWeergave=-1) or (st.InstellingSoortCategorie = @InstellingSoortCatWeergave))
and ((@CSVInstellingID = '') or (st.InstellingSoortID in (Select convert(int,Value) from dbo.Split(@CSVInstellingID,','))))
and ((@LocatieNaam = '') or (i.InstellingNaam like '%' + @LocatieNaam + '%') or (lo.LocatieNaam like '%' + @LocatieNaam + '%'))
and ((@PlaatsNaam = '') or (lo.LocatieBezoekPlaatsnaam like '%' + @PlaatsNaam + '%'))
order by
zk.zkorder DESC

end
-- er is wel een netwerk id, dus toon alleen alles voor dat netwerk
ELSE
BEGIN
select
zk.zkorderid as zkorderid,
zk.zkmoduleid as moduleid,
zk.zkorder as [order],
zk.zkzichtbaar as zichtbaar,
pr.ProductID as ProductID,
pd.ProductDetailsAantalPlaatsen as AantalPlaatsen,
pd.ProductDetailsAantalPlaatsenVrij as PlaatsenVrij,
pd.ProductDetailsAantalPlaatsenVrijWeergeven as AantalPlaatsenVrijWeergeven,
pd.ProductDetailsAantalPlaatsenVrijLaatstbijgewerkt as PlaatsenVrijLaatstbijgewerkt,
st.InstellingSoortID as InstellingSoortID,
st.InstellingSoortNaam as InstellingSoortNaam,
st.InstellingSoortSubNaam as InstellingSoortSubNaam,
bi.BasisIndelingNaam as BasisIndelingNaam,
lo.LocatieID as LocatieID,
lo.LocatieNaam as LocatieNaam,
lo.LocatieBezoekPlaatsnaam as LocatieBezoekPlaatsnaam,
lo.LocatieTelefoon as LocatieTelefoon,
lo.LocatieHoofdProvincieID as LocatieHoofdProvincieID,
lo.LocatiehoofdNetwerkid as LocatieHoofdNetwerkid,
i.InstellingID as InstellingID,
i.InstellingNaam as InstellingNaam
from
(select * from ZMZKOrder where Zkmoduleid = @moduleid) zk
right join ZMproduct pr on (zk.zkproductid = pr.productid)
inner join ZMInstellingSoort st on (pr.ProductInstellingSoortID = st.InstellingSoortID)
inner join ZMBasisIndeling bi on (pr.ProductBasisIndelingID = bi.BasisIndelingID)
inner join ZMLocatie lo on (pr.ProductLocatieID = lo.LocatieID)
left join ZMProductDetails pd on (pr.ProductProductDetailsID = pd.ProductDetailsID)
inner join ZMInstelling i on (lo.LocatieInstellingID = i.InstellingID)

where
pr.ProductPublicatieStatus = 1
and ((@InstellingSoortCatWeergave=-1) or (st.InstellingSoortCategorie = @InstellingSoortCatWeergave)) and ((@CSVInstellingID = '') or (st.InstellingSoortID in (Select convert(int,Value) from dbo.Split(@CSVInstellingID,','))))
and ((@LocatieNaam = '') or (i.InstellingNaam like '%' + @LocatieNaam + '%') or (lo.LocatieNaam like '%' + @LocatieNaam + '%'))
and ((@PlaatsNaam = '') or (lo.LocatieBezoekPlaatsnaam like '%' + @PlaatsNaam + '%'))
and ((lo.LocatiehoofdNetwerkid = @NetwerkID) or EXISTS (select * from ZMNetwerkPZProduct Where ZMNetwerkPZProduct.ProductID = pr.ProductID AND ZMNetwerkPZProduct.NetwerkPZID = @netwerkid))
order by
zk.zkorder DESC
End
end

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-04-16 : 14:01:03

You see, there is a subquery at the bottom, and for that it will make a difference:

EXISTS (select * from ZMNetwerkPZProduct Where ZMNetwerkPZProduct.ProductID = pr.ProductID

It used to FIRST check:
ZMProduct pr
left join ZMZKOrder zk on (zk.zkproductid = pr.productid)

And now it first checks

zkmoduleid = @moduleid

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-04-16 : 14:35:35

Humm, I'm not sure I'm following you: Given your query (part of it)

from 
(select * from ZMZKOrder where Zkmoduleid = @moduleid) zk 
right join ZMproduct pr on (zk.zkproductid = pr.productid)

Is the same as

FROM 
	ZMZKOrder zk 
RIGHT JOIN 
	ZMproduct pr 
	ON zk.zkproductid = pr.productid
	AND zk.Zkmoduleid = @moduleid

which is the same as

FROM 
	ZMproduct pr 
LEFT OUTER JOIN
	ZMZKOrder zk 
	ON zk.zkproductid = pr.productid
	AND zk.Zkmoduleid = @moduleid

So, I'm not sure how an EXISTS clause has an affect on your joins? Other than certain results get filtered out. Am I missing something?

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-04-16 : 14:55:25

Thats is exactly what the difference is, some records used to be filtered out, now they're not anymore.

The secret to creativity is knowing how to hide your sources. (Einstein)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources