join 2 tables (multiple rows) - 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)

join 2 tables (multiple rows)

Author

Topic

marek
Starting Member

34 Posts

Posted - 2009-06-13 : 18:31:25

Hallo

I need help, please. I want join 2 tables (TabCompany and TabSalesman)
For example:

TabCompany

id/Company
1/dell
2/canon
3/bros
4/hitachi

TabSalesman

id/salesman
1/mark
1/mark
2/alf
2/bob
2/cruis
2/alf
3/smith
3/cruise

results:

id/company/salesman
1/dell/mark
2/canon/alfbobcruise--->or: alf,bob,cruise
3/bros/smith,cruise
4/hitachi

thanks for syntax this specific(concrete) problem.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-14 : 01:41:00

[code]SELECT c.id,c.Company,s1.salesman
FROM TabCompany c
INNER JOIN (
SELECT DISTINCT id,
STUFF((SELECT DISTINCT ',' + salesman FROM TabSalesman
WHERE id=s.id
FOR XML PATH('')),1,1,'') AS salesman
FROM TabSalesman s
)s1
ON s1.id=c.id
[/code]

marek
Starting Member

34 Posts

Posted - 2009-06-14 : 04:51:49

thanks Visakh16

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-14 : 04:59:19

welcome

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2009-06-15 : 13:00:05

hi,
My case here is I have a table having my records say table with this structure

Table A
id/date/amount
1/200901/50
2/200905/400
1/200906/20

Table B
ID/date/days
1/200901/2
2/200901/5
1/200912/23
1/200809/5

How can I get the occurrence records in Table A from Table B base on Id and date?

I sign for fame not for shame but all the same, I sign my name.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-15 : 13:50:18

do you mean this?


SELECT a.id,a.date,a.amount,count(b.id)
FROM TableA a
LEFT JOIN TableB b
ON a.id=b.id
AND a.date=b.date
GROUP BY a.id,a.date,a.amount

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources