Combine Two Tables - 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)

Combine Two Tables

Author

Topic

ejbatu
Starting Member

21 Posts

Posted - 2010-07-08 : 11:30:25

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,

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-08 : 12:14:46

What should be the output in relation to your example data?

No, you're never too old to Yak'n'Roll if you're too young to die.

umniza
Starting Member

19 Posts

Posted - 2010-07-08 : 17:23:46

That's correct. TableA has 4 records with ID 102. TableB has 6. Cross product, which is inner join is 6*4=24. What did you want (expect) to show for 102?

Also it's because you did 'select *' - that means all fields from both tables. Try changing it to 'select distinct a.clientID' and see what you get then.

ejbatu
Starting Member

21 Posts

Posted - 2010-07-09 : 11:08:54

Thanks guys, I don't think it will work the way I wanted it to work for the exact reason you stated. Please disregard.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources