JOIN Help Needed (Maybe a SubQuery Too)

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 2000 Forums

SQL Server Development (2000)

JOIN Help Needed (Maybe a SubQuery Too)

Author

Topic

scripter73
Starting Member

2 Posts

Posted - 2004-12-28 : 12:56:04

Hi,

I'm actually a web programmer, but my SQL joins are really rusty right now.

This is a 1 to Many question. I separated the fields/fieldnames with bars.

I have 3 Tables I need to connect:

Table A = VENDORS (3 records)
----------------------------
VendorID | Name
1| Rooms To Go
2| TXU
3| Circuit City

Table B = CLIENT (8 records)
----------------------------
VendorID | ClientID
1| 20
1| 84
1| 2
1| 69
2| 20
2| 84
2| 2
2| 69

Table C = CLIENTNAME (100+ records)
----------------------------
ClientID | Clientname
20|Acme
84|Barnes
2|Baylor
69|Balda

I want my result set to look like:

VendorID(from VENDORS)|Name(Vendors)|AllClients(a derived column):
----------------------------------
1 |Rooms To Go |Acme, Barnes,Baylor,Balda
2 |TXU | Acme, Barnes,Baylor,Balda
3 |Circuit City

I need a little help on the joins, and in particular the sub-select I think I may need. The reason I need the clientnames together is for in the future when I search based on a substring.

Here's what I have so far, but this only connects the first two tables and also gives me more records than I'm looking for. The third table, Clientname is used to decode the client's name.

SELECT distinct
v.*,
c.*
FROM vendors v LEFT JOIN
( select * from client) c
on v.vendorid = c.vendorid

Thanks in advance for any help you can provide.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-28 : 13:11:02

>> The reason I need the clientnames together is for in the future when I search based on a substring.
ummmmm

see
http://www.mindsdoor.net/SQLTsql/CSVStringFromTableEntries.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

scripter73
Starting Member

2 Posts

Posted - 2004-12-28 : 13:21:05

Thanks nr, but I think my boss wants me to do it within the actual query.
I'll keep this in mind, though.

Thanks again.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-28 : 13:28:16

It's not possible in a single query (without a function as above) unless you limit the number of values.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources