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 3 tables

Author  Topic 

marek
Starting Member

34 Posts

Posted - 2009-06-15 : 07:11:51
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.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-15 : 07:51:11
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

34 Posts

Posted - 2009-06-15 : 08:54:49
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
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-15 : 09:06:51
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

34 Posts

Posted - 2009-06-15 : 12:51:05
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

52326 Posts

Posted - 2009-06-15 : 14:00:42
[code]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
[/code]
Go to Top of Page

marek
Starting Member

34 Posts

Posted - 2009-06-15 : 16:41:51
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

63 Posts

Posted - 2012-11-21 : 03:04:10
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
Go to Top of Page
   

- Advertisement -