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?
September 11, 2020 at 2:26 pm
Given any MSKU, what is the lookup logic for getting its Title?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 11, 2020 at 2:28 pm
The below is the part of the query shown above that looks up the title
(SELECT TOP 1 MemoText FROM StockItemMemo WHERE StockItemMemo.ItemID = StockItem.ItemId and MemoText like @title+'%') as Title
The MSKU isn't directly tied to a title except for one of the items that has that MSKU, if that makes sense. The data above shows this.
September 11, 2020 at 2:35 pm
DECLARE @title as VARCHAR(MAX) = 'Title:';
SELECT si.ItemID,
si.Code as SKU,
si.AnalysisCode12 as MSKU,
sim.Title
FROM StockItem si
OUTER APPLY(SELECT TOP (1) MemoText Title
FROM StockItemMemo sim
WHERE sim.ItemID = si.ItemId
AND sim.MemoText like @title+'%') sim
WHERE si.AnalysisCode7 = 'YES'
You might want to put an ORDER BY in the OUTER APPLY to get the right MemoText from the StockItemMemo table.
September 11, 2020 at 2:39 pm
Unfortunately, that is giving the same result as my query above:
ItemID SKU MSKU Title
-------------------- ------------------------------ ------------------------------------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
36414 SWR0060 Title:Professional C7 Steel Wire Rope Cutters (0.5mm - 4mm) Snips Fence Tool
14859140 4220.12.010 220.12.010 Title: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 Title:52mm Stainless Glass Adapter (10-20mm) - Metric and Wood Thread Balustrade Handrail
102244764 SWR-SPIGOT SWR-SPIGOTS Title: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
September 11, 2020 at 3:09 pm
If you don't want the rows with the NULL's to be returned just make it CROSS APPLY instead of OUTER APPLY.
If you want the rows with the NULLS to be present but don't want a NULL in the column just use COALESCE or the ISNULL function on the column.
I think you've misunderstood what I'm looking for.
Someone on another site has provided the below which is doing what I want:
DECLARE @title as VARCHAR(MAX) = 'Title:';
; WITH CTE AS
(
SELECT
AnalysisCode12 as MSKU,
MemoText as Title
FROM StockItemMemo SIM
JOIN StockItem SI
ON SIM.ItemID = SI.ItemId
WHERE MemoText like @title+'%'
)
SELECT
ItemID,
Code as SKU,
AnalysisCode12 as MSKU,
C.Title
From StockItem SI
LEFT JOIN CTE C
ON C.MSKU=SI.AnalysisCode12
WHERE AnalysisCode7 = 'YES'
September 11, 2020 at 3:16 pm
I understand what you're going for, but I need to confirm something about your data.
For each MSKU, will there never be more than one ItemID with a MemoText?
Cheers!
September 11, 2020 at 5:52 pm
In theory yes but in reality possibly not. In the real StockItemMemo table there is a column for the last updated datetime so I'm just going to do a TOP 1 and ORDER BY datetime DESC.
My last post has the solution to this anyways so I'm all good now 🙂
September 14, 2020 at 2:39 pm
Glad to hear you have something that seems to work.
That posted solution is a good example of why I asked my question.
If you have some MSKUs for which there are multiple ItemIDs with a MemoText, then you'd get multiple rows in the final result set for each ItemID associated with that MSKU, which may not be desired.
Cheers!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply