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 2012 Forums
 Transact-SQL (2012)
 IS IN JOIN?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

wafw1971
Yak Posting Veteran

75 Posts

Posted - 05/01/2013 :  08:44:14  Show Profile  Reply with Quote
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

Edited by - wafw1971 on 05/01/2013 09:05:19

James K
Flowing Fount of Yak Knowledge

3567 Posts

Posted - 05/01/2013 :  09:03:53  Show Profile  Reply with Quote
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
Go to Top of Page

wafw1971
Yak Posting Veteran

75 Posts

Posted - 05/01/2013 :  09:06:25  Show Profile  Reply with Quote
Hi James

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

Ta

W
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3567 Posts

Posted - 05/01/2013 :  10:52:55  Show Profile  Reply with Quote
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.
Go to Top of Page

wafw1971
Yak Posting Veteran

75 Posts

Posted - 05/01/2013 :  10:55:39  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3567 Posts

Posted - 05/01/2013 :  13:54:18  Show Profile  Reply with Quote
Can you try the code that I posted earlier? Seems like that should give you what you are looking for.
Go to Top of Page

wafw1971
Yak Posting Veteran

75 Posts

Posted - 05/02/2013 :  04:37:38  Show Profile  Reply with Quote
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]
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.07 seconds. Powered By: Snitz Forums 2000