SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 join 2 tables (multiple rows)		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

marek
Starting Member

Slovakia
34 Posts
Posted - 06/13/2009 :  18:31:25  Show Profile  Reply with Quote
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.
Edited by - marek on 06/13/2009 18:46:12

visakh16
Very Important crosS Applying yaK Herder

India
47040 Posts
Posted - 06/14/2009 :  01:41:00  Show Profile  Reply with Quote 
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
Go to Top of Page

marek
Starting Member

Slovakia
34 Posts
Posted - 06/14/2009 :  04:51:49  Show Profile  Reply with Quote
thanks Visakh16
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47040 Posts
Posted - 06/14/2009 :  04:59:19  Show Profile  Reply with Quote
welcome
Go to Top of Page

abacusdotcom
Posting Yak Master

Nigeria
130 Posts
Posted - 06/15/2009 :  13:00:05  Show Profile  Visit abacusdotcom's Homepage  Send abacusdotcom a Yahoo! Message  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47040 Posts
Posted - 06/15/2009 :  13:50:18  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000