IS IN JOIN? - SQL Server Forums

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

Transact-SQL (2012)

IS IN JOIN?

Author

Topic

wafw1971
Yak Posting Veteran

75 Posts

Posted - 2013-05-01 : 08:44:14

I have 3 tables:
- Major Colours - List High Level Colour Names
- Sub Colours - List Sub colours which are then matched to a Major Colour
- Silks - This has a list of silk names but have multiple Colours in the name (Blue Black White Crimson)

What I need to do is create a linked table matching all the colours from the 3 tables see SQL below. <y boss ahs advised me to use a 'IS IN' query but I have no idea what that is can you help?

SELECT s.Silks_Skey, mc.MajorColour_Skey
FROM Silks s INNER JOIN SubColour sc on sc.SubColour 'IS IN' s.SilksName
INNER JOIN MajorColour mc
ON sc.MajorColour = mc.MajorColour

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-01 : 09:03:53

Somewhat hard to figure out what you are want to do without some data and the table schema. Take a look at this article which would help you post the question in a manner that is easy for others to understand. http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

wafw1971
Yak Posting Veteran

75 Posts

Posted - 2013-05-01 : 09:06:25

Hi James

Wrote the original request in a hurry before lunch, I have updated my question I hope it helps.

Ta

W

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-01 : 10:52:55

Can you post some sample data for each of the tables? Not sure what you meant by "but have multiple Colours in the name". Are they comma-separated? Or just bunched up all together? I don't think IS IN (or rather IN) would work here. You would need to do pattern matching of some kind. For example, something like this:

.....
FROM Silks s INNER JOIN SubColour sc on s.SilksName LIKE '%'+sc.SubColour+'%'

But I don't know for sure, I am just guessing. Sample data would help.

wafw1971
Yak Posting Veteran

75 Posts

Posted - 2013-05-01 : 10:55:39

Silk_Skey Name
1 Black White Checks Yellow Arms
2 Black Crimson Stripes
3 Crimson Yellow Stripes

Sub Colour Major Colour
Black Black
White White
Yellow Yellow
Crimson Red

MajorColour_Skey Major Colour
1 Black
2 White
3 Yellow
4 Red

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-01 : 13:54:18

Can you try the code that I posted earlier? Seems like that should give you what you are looking for.

wafw1971
Yak Posting Veteran

75 Posts

Posted - 2013-05-02 : 04:37:38

This is the answer folks, Thanks for your help

Select S.[Silks_Skey], MC.[MajorColour_Skey]
from [dbo].[Silks] S
inner join [dbo].[SubColour] SC on CHARINDEX(SC.[SubColour],S.[SilksName]) <> 0
inner join [dbo].[MajorColour] MC on SC.[MajorColour] = MC.[MajorColour]

UNION ALL

Select S.[Silks_Skey], MC.[MajorColour_Skey]
from [dbo].[Silks] S
inner join [dbo].[MajorColour] MC on CHARINDEX(MC.[MajorColour],S.[SilksName]) <> 0

ORDER BY S.[Silks_Skey]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources