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