Problem with join - 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

Author

Topic

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-04-15 : 06:17:44

Hi, I'm trying to do a join between a couple of tables.
I'm having a problem with 1 join between the products an the order (as in sort order, not ordering of products) table.

the order table looks like this:
ZMZKOrder:
- ZKorderID
- ZKModuleID
- ZKProductID
- ZKOrder
- ZKZichtbaar

The products tabel kinda looks like this
ZMProduct
- ProductID
- ProductName
- etc.

So I want to join the two with the productId, the problem is, the products table already exists, and is filled with products.
And the order table is empty. The thing runs in a CMS which uses modules. So I will add a parameter with moduleid

So I am using a param @ModuleID int
when it first runs, I want the module to return:
ZKorderId: NULL
ZKModuleID: NULL
ZKProductID: Null
ZKORder: Null
ZKZichtbaar: null
Productid: 123 (some id)
ProductName: name (some name)

Im using:

select
zk.zkorderid as zkorderid,
zk.zkmoduleid as moduleid,
zk.zkorder as [order],
zk.zkzichtbaar as zichtbaar,
pr.ProductID as ProductID
etc.

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

where
((zk.zkmoduleid = @moduleid) or (zk.zkmoduleid = Null))
etc.

But it does not seem to work...
Any ideas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-15 : 06:22:43

Change like this & see:-

select 
zk.zkorderid as zkorderid,
zk.zkmoduleid as moduleid,
zk.zkorder as [order],
zk.zkzichtbaar as zichtbaar,
pr.ProductID as ProductID
etc.

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

where 
((zk.zkmoduleid = @moduleid) or (zk.zkmoduleid IS Null))

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-04-15 : 06:30:01

Hi, thanks, I'll try.

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

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-04-15 : 06:48:14

Well, it seems to work, except that I also use a subquery which does not work now.
It looks like this:
and
((lo.LocatiehoofdNetwerkid = @NetwerkID) or EXISTS (select * from ZMNetwerkPZProduct Where ZMNetwerkPZProduct.ProductID = pr.ProductID AND ZMNetwerkPZProduct.NetwerkPZID = @netwerkid))

When I leave out the:

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

part, it will return records from the substring, else it will only return the (lo.LocatiehoofdNetwerkid = @NetwerkID) records.

Any ideas?

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

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-04-15 : 06:55:55

The whole thing looks like this:

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.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
ZMZKOrder 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
((zk.zkmoduleid = @moduleid) or (zk.zkmoduleid = Null))
and 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
ZMZKOrder 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
((zk.zkmoduleid = @moduleid) or (zk.zkmoduleid is Null))
and 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

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

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-04-15 : 07:04:51

I think the problem can exist because there already is another record in ZMZKorder with the same productid, but for a different module.
So in ZKOrder it says:
ZKOrderID: 79
ZKModuleID: 8874
ZKProductID: 481
ZKOrder: 31
ZKZIchtbaar: true

And when I run the SP for another moduleid, which will also return productid 481 via the subquery
It sees a record because:
zk.zkproductid = pr.productid

But it will not show because:
zk.zkmoduleid = @moduleid is not true
Because this is for a different moduleid, which calls the same row

AND
zk.zkmoduleid is Null is also not true, because it has a value, only not the right one.

So how in the #$@% can I solve that!?!?!?!

I only want it to return a record with all fields for ZMZKOrder set to Null.

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-04-15 : 07:13:10

So we have

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 also is true.

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-15 : 11:26:46

quote:
Originally posted by trouble2

I think the problem can exist because there already is another record in ZMZKorder with the same productid, but for a different module.
So in ZKOrder it says:
ZKOrderID: 79
ZKModuleID: 8874
ZKProductID: 481
ZKOrder: 31
ZKZIchtbaar: true

And when I run the SP for another moduleid, which will also return productid 481 via the subquery
It sees a record because:
zk.zkproductid = pr.productid

But it will not show because:
zk.zkmoduleid = @moduleid is not true
Because this is for a different moduleid, which calls the same row

AND
zk.zkmoduleid is Null is also not true, because it has a value, only not the right one.

So how in the #$@% can I solve that!?!?!?!

I only want it to return a record with all fields for ZMZKOrder set to Null.

if its for another moduleid it doesnt need to be listed as you are looking for results of passed on moduleid.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-15 : 11:28:38

quote:
Originally posted by trouble2

So we have

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 also is true.

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

Thats what you are checking by the above condition.Please post you table DDL and explain what you are really looking for with some sample data.

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-04-15 : 11:31:44

Could you check my other post (problem with join (part 2)).
Perhaps this will clear it up.

Thanks

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