SQL Server grab data for multiple rows from two tables

joshswr · September 11, 2020, 1:45pm

So here's the two tables I'm working with

StockItem:

ItemID               SKU                           MSKU
-------------------- ------------------------------ -------------------------
36414                SWR0060                        
14859140             4220.12.010                    220.12.010
14860211             6220.12.010                    220.12.010
47717413             6000.22.010                    6000.22.XXX
102244764            SWR-SPIGOT                     SWR-SPIGOTS
115377648            SWR-SPIGOTBLA                  SWR-SPIGOTS
115381142            SWR-SPIGOTCOP                  SWR-SPIGOTS
116112478            6000.22.030                    6000.22.XXX
116112701            6000.22.050                    6000.22.XXX
119263276            SWR-SPIGOTAG                   SWR-SPIGOTS

StockItemMemo:

ItemID               MemoText
-------------------- ----------------------------------------
36414                Title:Professional C7 Steel Wire Rope Cutters (0.5mm - 4mm) Snips Fence Tool
14859140             Title:Stainless Steel Base Plate for 42.4mm Balustrade Handrail Railing Component
47717413             Title:52mm Stainless Glass Adapter (10-20mm) - Metric and Wood Thread Balustrade Handrail
102244764            Title:Stainless Steel Spigot 10-12mm Glass Handrail Balustrade Railing Balcony Clamp

Currently, when I run the query below, the title is displayed for the primary items which have the title directly joined to them, but the items joined to those items by the MSKU field show NULLs

DECLARE @title as VARCHAR(MAX) = 'Title:';

SELECT
ItemID,
Code as SKU, 
AnalysisCode12 as MSKU,
(SELECT TOP 1 MemoText FROM StockItemMemo WHERE StockItemMemo.ItemID = StockItem.ItemId and MemoText like @title+'%') as Title
From StockItem WHERE AnalysisCode7 = 'YES'



ItemID               SKU                            MSKU                                                         Title
-------------------- ------------------------------ ------------------------------------------------------------ ----------------------------------------------
36414                SWR0060                                                                                     Professional C7 Steel Wire Rope Cutters (0.5mm - 4mm) Snips Fence Tool
14859140             4220.12.010                    220.12.010                                                   Stainless Steel Base Plate for 42.4mm Balustrade Handrail Railing Component
14860211             6220.12.010                    220.12.010                                                   NULL
47717413             6000.22.010                    6000.22.XXX                                                  52mm Stainless Glass Adapter (10-20mm) - Metric and Wood Thread Balustrade Handrail
102244764            SWR-SPIGOT                     SWR-SPIGOTS                                                  Stainless Steel Spigot 10-12mm Glass Handrail Balustrade Railing Balcony Clamp
115377648            SWR-SPIGOTBLA                  SWR-SPIGOTS                                                  NULL
115381142            SWR-SPIGOTCOP                  SWR-SPIGOTS                                                  NULL
116112478            6000.22.030                    6000.22.XXX                                                  NULL
116112701            6000.22.050                    6000.22.XXX                                                  NULL
119263276            SWR-SPIGOTAG                   SWR-SPIGOTS                                                  NULL

I need the title from the main SKU to show for all the items with a matching MSKU too, so the below is the result I want:

ItemID               SKU                            MSKU                                                         Title
-------------------- ------------------------------ ------------------------------------------------------------ ----------------------------------------------
36414                SWR0060                                                                                     Professional C7 Steel Wire Rope Cutters (0.5mm - 4mm) Snips Fence Tool
14859140             4220.12.010                    220.12.010                                                   Stainless Steel Base Plate for 42.4mm Balustrade Handrail Railing Component
14860211             6220.12.010                    220.12.010                                                   Stainless Steel Base Plate for 42.4mm Balustrade Handrail Railing Component
47717413             6000.22.010                    6000.22.XXX                                                  52mm Stainless Glass Adapter (10-20mm) - Metric and Wood Thread Balustrade Handrail
102244764            SWR-SPIGOT                     SWR-SPIGOTS                                                  Stainless Steel Spigot 10-12mm Glass Handrail Balustrade Railing Balcony Clamp
115377648            SWR-SPIGOTBLA                  SWR-SPIGOTS                                                  Stainless Steel Spigot 10-12mm Glass Handrail Balustrade Railing Balcony Clamp
115381142            SWR-SPIGOTCOP                  SWR-SPIGOTS                                                  Stainless Steel Spigot 10-12mm Glass Handrail Balustrade Railing Balcony Clamp
116112478            6000.22.030                    6000.22.XXX                                                  52mm Stainless Glass Adapter (10-20mm) - Metric and Wood Thread Balustrade Handrail
116112701            6000.22.050                    6000.22.XXX                                                  52mm Stainless Glass Adapter (10-20mm) - Metric and Wood Thread Balustrade Handrail
119263276            SWR-SPIGOTAG                   SWR-SPIGOTS                                                  Stainless Steel Spigot 10-12mm Glass Handrail Balustrade Railing Balcony Clamp

What would be the best way of doing this?

harishgg1 · September 11, 2020, 2:12pm

hi

i have created data script for use in this forum

declare @stock_item table (ItemID int,SKU  varchar(100),MSKU  varchar(100)  null )
insert into @stock_item values 
(36414     ,'SWR0060       ', null			 ),
(14859140  ,'4220.12.010   ',' 220.12.010 '	 ),
(14860211  ,'6220.12.010   ',' 220.12.010 '	 ),
(47717413  ,'6000.22.010   ',' 6000.22.XXX'	 ),
(102244764 ,'SWR-SPIGOT    ',' SWR-SPIGOTS'	 ),
(115377648 ,'SWR-SPIGOTBLA ',' SWR-SPIGOTS'	 ),
(115381142 ,'SWR-SPIGOTCOP ',' SWR-SPIGOTS'	 ),
(116112478 ,'6000.22.030   ',' 6000.22.XXX'	 ),
(116112701 ,'6000.22.050   ',' 6000.22.XXX'	 ),
(119263276 ,'SWR-SPIGOTAG  ',' SWR-SPIGOTS'	 )

select 'Stock Item', * from @stock_item

declare @Stock_Item_Memo table (ItemID int,MemoText varchar(500))
insert into @Stock_Item_Memo values 																 
(36414    ,'Title:Professional C7 Steel Wire Rope Cutters (0.5mm - 4mm) Snips Fence Tool			  '),
(14859140 ,'Title:Stainless Steel Base Plate for 42.4mm Balustrade Handrail Railing Component		  '),
(47717413 ,'Title:52mm Stainless Glass Adapter (10-20mm) - Metric and Wood Thread Balustrade Handrail '),
(102244764 ,'Title:Stainless Steel Spigot 10-12mm Glass Handrail Balustrade Railing Balcony Clamp	  ')

select 'Stock Item Memo ', * from @Stock_Item_Memo

harishgg1 · September 11, 2020, 3:46pm

hi

i have done this in one way !!! i will explore other ways and better ways later
Please see if its what you are looking for or like

; with cte as 
(
select a.ItemID , a.SKU , a.MSKU , b.MemoText from @stock_item a left join @Stock_Item_Memo b on a.ItemID = b.ItemID 
) , cte_get_inter_set
as 
(
select isnull(msku,1111111111) as msku, MemoText from cte where  MemoText is not null
)
select 
      a.itemid 
	, a.SKU 
	, a.MSKU 
	, b.MemoText 
from 
  cte a 
    left join 
  cte_get_inter_set b 
         on isnull(a.MSKU,1111111111) = b.MSKU 
 order by 
    itemid

harishgg1 · September 12, 2020, 5:28am