Combine Two Tables

[ejbatu]

Hi,

I'm trying to select distinct records from TableA and TableB below, but I'm getting multiple records. For example ClientID 102 returns 24 record using the query below. What is the best way to avoid duplicated records.

select *
from TableA a inner join TableB on (a.clientid = b.clientid)
order by a.ClientID

TableA
TAiD ClientID	LicenseID   InUse   ProductVersion
533	102	1	    8500	4.5.2
534	102	2	     600	4.5.8
535	102	3	     461	4.5.2
536	102	4	      450	4.5.6
527	103	1	     4649	5.2.2	
528	103	2	      1		5.2.2	
529	103	3	     132	5.2.2	
530	103	6	      0		5.2.10	
531	103	4	      6		5.2.2	
532	103	8	    4486	5.2.10	

TableB
TBiD	ClientID  ProductNM	ProdDesc    ProdID   	ProdType
12	102	ABC-DC1		ABC DC	   192-162	DC
13	102	ABC-DC2		ABC DB2	   192-163	DC
14	102	ABC-TT 		ABC Tele   192-166	TT
15	102	ABC-WE1		ABC We1	   192-164	WE
16	102	ABC-WE2		ABC We2	   192-165	WE
17	102	ABC-Test	ABC Test   192-167	TEST
18	103	AVA-NOS		Ava NOS	   192-513	NOS
19	103	AVA-TEST	Ava Test   192-561	TEST
20	103	AVA-WEB		Ava Web	   192-560	WEB

Thanks,

[ck9663]

Because you use "*".

If you want to return all distinct ClientId you're going to have to return just the ClientId. If you include any other column, the distinct combination of the columns on your SELECT clause will be returned.

select distinct a.clientid
from TableA a 
inner join TableB on (a.clientid = b.clientid)
order by a.ClientID

That will only return all ClientId that exists on both table.

Happy Coding!!!

~~ CK

[ejbatu]

Thank you, but I don't think it will ever work the way I expected it to work.

[viktorka]

You have 24 records for client with id = 102 because you have 24 different products for this client. If you want only one record you have to define what product you want to see

[Delerna]

you have 4 records in table a for clientID=102

you have 6 records in table b for clientID=102

if you join table a to table b using clientID as the only join parameter, then each of the 4 records in table a will join to each of the 6 records in table b
in other words you will have 6 lots of each of the 4 records in table a because each one joins to each of the 6 records in table b


4 * 6 = 24 records.



From your confusion I surmise that the relationship must be more than simply the ClientId and yet I see no other fields in the 2 tables that indicate a relationship between them.

If my surmation is correct then
What is the other relationship between the 2 tables besides ClientID
You need to identify it and include it as a join condition in addition to ClientID