Help with a join

Question

Help with a join

TSQL Tryer

SSCrazy Eights

Points: 8869
More actions
September 1, 2015 at 6:57 am

#304023

So, I have a table "table a"
This has two columns. "username" and "companyid"
A sample of data for "companyid" is - "1,2,3,4".
How would I go about join from "table a" to "table b" which holds the company description?
I have only ever joined to a table if companyid in table a has one entry, not multiple.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

TSQL Tryer (9/1/2015)
So, I have a table "table a"
This has two columns. "username" and "companyid"
A sample of data for "companyid" is - "1,2,3,4".
How would I go about join from "table a" to "table b" which holds the company description?
I have only ever joined to a table if companyid in table a has one entry, not multiple.

SELECT ...

FROM tablea a

INNER JOIN tableb b

ON b.companyid = a.companyid

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Y.B. SSChampion Points: 11649 More actions · Answer 2

You shouldn't have multiple CompanyID's stored this way. You'll want to normalize your data. You can't do joins that way unless the actual ID in tableB was "1,2,3,4".

SELECT quote FROM brain WHERE original = 1
0 rows returned

TSQL Tryer SSCrazy Eights Points: 8869 More actions · Answer 3

I've not designed the database or the table. I'm simply trying to get a report out of it

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

yb751 (9/1/2015)
You shouldn't have multiple CompanyID's stored this way. You'll want to normalize your data. You can't do joins that way unless the actual ID in tableB was "1,2,3,4".

Good catch yb.

You will need to split the string up, resolve those ID's. Use the tried and tested "House Function":

SELECT ...

FROM tablea a

CROSS APPLY dbo.DelimitedSplit8k (a.CompanyID, ',') split

INNER JOIN tableb b

ON b.companyid = split.item

http://www.sqlservercentral.com/articles/Tally+Table/72993/[/url]

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Y.B. SSChampion Points: 11649 More actions · Answer 5

TSQL Tryer (9/1/2015)
I've not designed the database or the table. I'm simply trying to get a report out of it

In that case I suggest breaking out tableA in a temp table before joining it on tableB.

Here is an excellent splitter you could use.

http://www.sqlservercentral.com/articles/Tally+Table/72993/

SELECT quote FROM brain WHERE original = 1
0 rows returned

Y.B. SSChampion Points: 11649 More actions · Answer 6

ChrisM@Work (9/1/2015)
yb751 (9/1/2015)
You shouldn't have multiple CompanyID's stored this way. You'll want to normalize your data. You can't do joins that way unless the actual ID in tableB was "1,2,3,4".
Good catch yb.
You will need to split the string up, resolve those ID's. Use the tried and tested "House Function":
SELECT ...
FROM tablea a
CROSS APPLY dbo.DelimitedSplit8k (a.CompanyID, ',') split
INNER JOIN tableb b
ON b.companyid = split.item
http://www.sqlservercentral.com/articles/Tally+Table/72993/[/url]

NP...I'm the worse for missing that kind of stuff usually. 😉

Noticed you beat me to the Split8K sermon. LOL At least the OP should have everything he/she needs now.

SELECT quote FROM brain WHERE original = 1
0 rows returned