Complex Join Query 1

[megmogg]

Hi all

I have an issue creating a query, which is arkward.

Basically, I have a table containing items for sale which is linked to a seperate table containing codes for the items. But some clients require their own descriptions and these descriptions could relate to more than one of my codes. Therefore, I have the following tables:

Client Table
ClientID (primary)
ClientName

Mapping_Client Table
Mapping_ClientID (primary)
ClientID
ClientDesc

Mapping_Code Table
Mapping_Codes_ID (primary)
Mapping_Client_ID
Code_ID

Codes Table
CodeID (primary)
CodeDesc

Item Table
ItemID (primary)
CodeID
Desc



What I want is a query to pull back all the items and their corresponding client descriptions from the Mapping_Client table. However, if there is no client description, then I want to use the description from the Code table. The SQL can just have NULLs in either of these two fields as I can check in my code for this condition.

I started the query using basic left joins, but realised it was pulling back duplicate rows (which cannot happen). I could have used an inner join, but this relied upon a client desc being present.

My query is as follows:

select item.itemid, mapping_client.clientdesc, codes.desc
  from item
  left join codes on codes.codeid=item.codeid
  left join mapping_code on mapping_code.codeid=codes.codeid
  left join mapping_client on mapping_client.mapping_clientid=mapping_code.mapping_clientid and mapping_client.clientid=10

Basically, the query above wil pull back duplicates because the 2nd join is pulling back other client info and the client join is the 3rd join. If I used inner join's then it works, but does not cater for clients with no client desc's and in this case I need to use the codes desc.


Help in the right direction would be appreciated.


Thanks

 

[amrita418]

Does this help?

select distinct item.itemid, case when mapping_client.clientdesc is null then codes.desc else mapping_client.clientdesc end
  from item
  left join codes on codes.codeid=item.codeid
  left join mapping_code on mapping_code.codeid=codes.codeid
  left join mapping_client on mapping_client.mapping_clientid=mapping_code.mapping_clientid 
and mapping_client.clientid=10

Regards,
AA

 

[megmogg]

Thanks for the reply.

Sorry, no, it would still bring back duplicates because of the joins.

One solution I thought of was to do two queries, one is a full join to return matches then another query to return unmatched then union the two. Don't really want to do this though if it can be helped.

Any other ideas welcomed.

 

[amrita418]

Can you post some sample data?

Regards,
AA

 

[maswien]

try this:

select item.codeID, Mapping_Client.ClientDesc as desc
from item inner join Mapping_Code
      on item.CodeID = Mapping_Code.Code_ID
    inner join Mapping_Client
      on Mapping_Code.Mapping_Client_ID = Mapping_Client.Mapping_Client_ID
union
select item.codeID, codes.Desc as desc 
from item inner join codes
 on item.codeID = codes.codeID

 

[megmogg]

CREATE TABLE [dbo].[codes] (
	[codeid] [int] NOT NULL ,
	[codedesc] [varchar] (30) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[item] (
	[itemid] [int] NOT NULL ,
	[codeid] [int] NULL ,
	[desc] [varchar] (30) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[mapping_client] (
	[mapping_clientid] [int] NOT NULL ,
	[clientid] [int] NULL ,
	[clientdesc] [varchar] (30) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[mapping_codes] (
	[mapping_codes_id] [int] NOT NULL ,
	[mapping_clientid] [int] NULL ,
	[codeid] [int] NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[codes] ADD 
	CONSTRAINT [PK_codes] PRIMARY KEY  CLUSTERED 
	(
		[codeid]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[item] ADD 
	CONSTRAINT [PK_item] PRIMARY KEY  CLUSTERED 
	(
		[itemid]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[mapping_client] ADD 
	CONSTRAINT [PK_mapping_client] PRIMARY KEY  CLUSTERED 
	(
		[mapping_clientid]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[mapping_codes] ADD 
	CONSTRAINT [PK_mapping_codes] PRIMARY KEY  CLUSTERED 
	(
		[mapping_codes_id]
	)  ON [PRIMARY] 
GO

The data for the tables is

insert into mapping_client (mapping_clientid, clientid, clientdesc)
	VALUES (1, 10, 'Code 2 - Client 10')

insert into mapping_client (mapping_clientid, clientid, clientdesc)
	VALUES (2, 10, 'Code 1 - Client 10')

insert into mapping_client (mapping_clientid, clientid, clientdesc)
	VALUES (3, 20, 'Code 2 - Client 20')

insert into mapping_client (mapping_clientid, clientid, clientdesc)
	VALUES (4, 20, 'Code 1 - Client 20')

insert into mapping_codes (mapping_codes_id, mapping_clientid, codeid) 
	VALUES (1, 2, 1)

insert into mapping_codes (mapping_codes_id, mapping_clientid, codeid) 
	VALUES (2, 1, 2)

insert into mapping_codes (mapping_codes_id, mapping_clientid, codeid) 
	VALUES (3, 3, 2)

insert into mapping_codes (mapping_codes_id, mapping_clientid, codeid) 
	VALUES (4, 4, 1)

insert into codes (codeid, codedesc)
	values (1, 'Code 1')

insert into codes (codeid, codedesc)
	values (2, 'Code 2')

insert into codes (codeid, codedesc)
	values (3, 'Code 3')

insert into item (itemid, codeid, [desc])
	VALUES (1, 1, 'Item 1')

insert into item (itemid, codeid, [desc])
	VALUES (2, 2, 'Item 2')

insert into item (itemid, codeid, [desc])
	VALUES (3, 3, 'Item 3')

insert into item (itemid, codeid, [desc])
	VALUES (4, 4, 'Item 4')

And the query I cannot get to work is

select item.itemid, mapping_client.clientdesc, codes.codedesc
  from item
  left join codes on codes.codeid=item.codeid
  left join mapping_codes on mapping_codes.codeid=codes.codeid
  left join mapping_client on mapping_client.mapping_clientid=mapping_codes.mapping_clientid and mapping_client.clientid=10

This query pulls out the data:

[u]item id   clientdesc           codedesc[/u]
1         Code 1 - Client 10   Code 1
1         NULL                 Code 1
2         Code 2 - Client 10   Code 2
2         NULL                 Code 2
3         NULL                 Code 3
4         NULL                 NULL

From this data, you can see item 1 appears twice because of the join. I want item 1 to appear once and I can only achieve this with a inner join.
However, I also want item 3 and item 4 to appear and cannot do this with an inner join.

Bit long winded, but if someone can find teh time to help me out, it would be appreciated.

Thanks

 

[MisterMo]

select item.itemid, mapping_client.clientdesc, codes.codedesc
from item
join codes on codes.codeid=item.codeid
join mapping_codes on mapping_codes.codeid=codes.codeid
join mapping_client on mapping_client.mapping_clientid=mapping_codes.mapping_clientid and mapping_client.clientid=10
union
select item.itemid, mapping_client.clientdesc, codes.codedesc
from item
left join codes on codes.codeid=item.codeid
left join mapping_codes on mapping_codes.codeid=codes.codeid
left join mapping_client on mapping_client.mapping_clientid=mapping_codes.mapping_clientid and mapping_client.clientid=10
where item.itemid not in(select item.itemid
from item
join codes on codes.codeid=item.codeid
join mapping_codes on mapping_codes.codeid=codes.codeid
join mapping_client on mapping_client.mapping_clientid=mapping_codes.mapping_clientid and mapping_client.clientid=10)

Mo

 

[megmogg]

Thanks very much, that deserves the star.

A question regarding the union, I always thought or led to believe it was performance hogging and should try to avoid it.

In this case, would a UNION ALL be better as there shouldn't be duplicates in the query written by Mo?
That would stop the extra processing for checking duplciates in the Union, or would it not really matter under these circumstances?

 

[MisterMo]

megmogg

there is nothing wrong with unions they are just a number of queries joined together, the real issue is the check for duplicates


here is another option

SELECT     dbo.item.itemid, MIN(dbo.mapping_client.clientdesc) AS Expr1, dbo.codes.codedesc
FROM         dbo.mapping_client RIGHT OUTER JOIN
                      dbo.mapping_codes ON dbo.mapping_client.mapping_clientid = dbo.mapping_codes.mapping_clientid RIGHT OUTER JOIN
                      dbo.item LEFT OUTER JOIN
                      dbo.codes ON dbo.codes.codeid = dbo.item.codeid ON dbo.mapping_codes.codeid = dbo.codes.codeid
GROUP BY dbo.item.itemid, dbo.codes.codedesc

Mo

 

[megmogg]

Thanks for the alternative, I'll give it a try.

You mentioned check for duplicates.

Therefore, should I use UNION ALL?

Thanks

 

[MisterMo]

No the nested select statemet will do that

Mo

 

[megmogg]

Thanks

Where would I put the clientid in the second statement?