Understanding what the query is? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Understanding what the query is?

Author

Topic

wafw1971
Yak Posting Veteran

75 Posts

Posted - 2013-05-03 : 05:15:12

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

52326 Posts

Posted - 2013-05-03 : 05:35:31

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

UnemployedInOz
Yak Posting Veteran

54 Posts

Posted - 2013-05-03 : 05:38:55

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

wafw1971
Yak Posting Veteran

75 Posts

Posted - 2013-05-03 : 05:49:52

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]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-03 : 06:04:32

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

wafw1971
Yak Posting Veteran

75 Posts

Posted - 2013-05-03 : 06:08:49

Thank you so much Visakh that's exactly right.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-03 : 06:18:56

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources