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 3 tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

marek
Starting Member

Slovakia
34 Posts

Posted - 06/15/2009 :  07:11:51  Show Profile  Reply with Quote
Pleas I need help.

TabCompany

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

TabResponsibility

id/num
1/213
1/213
2/365
2/420
2/560
2/365
3/680
3/680

TabEmployees

num/surname

213/mark
365/alf
420/bob
560/cruise
680/smith

results:

id/company/surname
1/dell/mark
2/canon/alf,bob,cruise
3/bros/smith
4/hitachi

TabCompany.id=TabResponsibility.id and TabResponsibility.num=TabEmployees.num

It is possible to join this tables?
thanks for syntax

Thanks very much.

Edited by - marek on 06/15/2009 12:51:32

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 06/15/2009 :  07:51:11  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
This looks exactly the same as your previous post:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=127546

What have you tried so far?


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

marek
Starting Member

Slovakia
34 Posts

Posted - 06/15/2009 :  08:54:49  Show Profile  Reply with Quote
It is not same. There are 3 tables, no 2 tables. It before is one report(resolved, thanks visakh16), and this problem is second report.
I have one table called "betweentab. TabResponsibility" and I dont know how I give into syntax.
Could you please help me with this problem? I cant solve it alone.

Thanks
Go to Top of Page

bklr
Flowing Fount of Yak Knowledge

India
1693 Posts

Posted - 06/15/2009 :  09:06:51  Show Profile  Reply with Quote
try this
SELECT c.id,c.Company,e.num,e.surname into #temp
FROM TabCompany c
INNER JOIN TabResponsibility r ON r.id = c.id
INNER JOIN TabEmployees e ON e.num = r.num

SELECT id,company,STUFF((SELECT ',' + surname FROM #temp WHERE num = t.num FOR XML PATH('')),1,1,'') AS surname
FROM #temp t
Go to Top of Page

marek
Starting Member

Slovakia
34 Posts

Posted - 06/15/2009 :  12:51:05  Show Profile  Reply with Quote
it is not good, result is:

1/dell/mark
1/dell/mark
2/canon/alf
2/canon/alf
2/canon/bob
2/canon/cruise
3/bros/smith
3/bros/smith

result must by this format:

id/company/surname
1/dell/mark
2/canon/alf,bob,cruise
3/bros/smith
4/hitachi

pleas if is possible without temp, thanks

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/15/2009 :  14:00:42  Show Profile  Reply with Quote
SELECT c.id,c.Company,STUFF((SELECT DISTINCT ','+e.surname
           FROM TabResponsibility r
           INNER JOIN TabEmployees e
           ON e.num=r.num
           WHERE r.id=c.id
           FOR XML PATH('')),1,1,'')
FROM TabCompany c

Go to Top of Page

marek
Starting Member

Slovakia
34 Posts

Posted - 06/15/2009 :  16:41:51  Show Profile  Reply with Quote
visakh16 realy realy big thanks...Thanks also "bklr"

This is exactly what I want to do.

good look
Go to Top of Page

Gekko
Yak Posting Veteran

Slovenia
63 Posts

Posted - 11/21/2012 :  03:04:10  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

SELECT c.id,c.Company,STUFF((SELECT DISTINCT ','+e.surname
           FROM TabResponsibility r
           INNER JOIN TabEmployees e
           ON e.num=r.num
           WHERE r.id=c.id
           FOR XML PATH('')),1,1,'')
FROM TabCompany c



Mr. visakh16
What if I want insert cast and convert e.surname on varchar?
quote:

SELECT c.id,c.Company, salesman= CAST STUFF((SELECT DISTINCT ','+CONVERT(varchar, e.surname)
FROM TabResponsibility r
INNER JOIN TabEmployees e
ON e.num=r.num
WHERE r.id=c.id
FOR XML PATH('')),1,1,'') as varchar (25)
FROM TabCompany c


quote has error. probably a mistake "(" or sequence of commands

big thanks

Edited by - Gekko on 11/21/2012 03:14:55
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.2 seconds. Powered By: Snitz Forums 2000