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)
 Understanding what the query is?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

wafw1971
Yak Posting Veteran

75 Posts

Posted - 05/03/2013 :  05:15:12  Show Profile  Reply with Quote
This is going to be a strange request, can someone explain in plain English what the query below is doing especially the <>0 bit.

Select S.[Silks_Skey], MC.[MajorColour_Skey], MC.[MajorColour]
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]


Thanks

W

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 05/03/2013 :  05:35:31  Show Profile  Reply with Quote
its returning majorcolor details from the MajorColor table based on the Subcolour values that are contained inside the SilksName field in Silks table. Obviously the field will have multiple colors stored and it will return majorcolor details for each included color.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

UnemployedInOz
Yak Posting Veteran

Australia
54 Posts

Posted - 05/03/2013 :  05:38:55  Show Profile  Reply with Quote
If you used the following query
Select SC.[SubColour],S.[SilksName]
from [dbo].[Silks] S
inner join [dbo].[SubColour] SC on CHARINDEX(SC.[SubColour],S.[SilksName]) <> 0



Each row in the SubColour table SubColour field will be compared to each row in the Silks table SilksName field
and the combination of the two will only be used if the field SC.SubColour value is in S.SilkName field.

SILKS.SilkName
abcd
efgh
ijkl

SubColour.SubColour
a
j
k
z

Result will be
a abcd
j ijkl
k ijkl
Go to Top of Page

wafw1971
Yak Posting Veteran

75 Posts

Posted - 05/03/2013 :  05:49:52  Show Profile  Reply with Quote
Hi Oz and Visakh

Thank you so much, now the real problem I have. I need to remove the union from the query below and make one query to show all Major colours related to silks name using the major colour and sub colour tables.

Can it be done?


Select S.[Silks_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

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 05/03/2013 :  06:04:32  Show Profile  Reply with Quote
this may be?

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



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

wafw1971
Yak Posting Veteran

75 Posts

Posted - 05/03/2013 :  06:08:49  Show Profile  Reply with Quote
Thank you so much Visakh that's exactly right.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 05/03/2013 :  06:18:56  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
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.09 seconds. Powered By: Snitz Forums 2000