July 12, 2012 at 12:03 pm
Is the statement below correct?
I.SKU will be used to retrieve a record from from ITEMMAST when either J.SKU=I.SKU or J.ITEMUPC=I.SKU
FROM
Evy_RH_Objects.dbo.RETAIL_SALES I
INNER JOIN RH2007_EvyLive.dbo.ITEMMAST J on J.SKU=I.SKU or J.ITEMUPC=I.SKU
July 12, 2012 at 12:09 pm
Looks good to me. The real test is does the query return what you expect?
July 12, 2012 at 12:21 pm
Thanks. I do have a problem with the output but I thought it might be related to using OR with the FROM clause. I will keep looking.
July 12, 2012 at 12:28 pm
syntax is correct but does it bring back correct records?
this may be better
select .........
FROM
Evy_RH_Objects.dbo.RETAIL_SALES I
INNER JOIN RH2007_EvyLive.dbo.ITEMMAST J on J.SKU=I.SKU
union
select .........
FROM
Evy_RH_Objects.dbo.RETAIL_SALES I
INNER JOIN RH2007_EvyLive.dbo.ITEMMAST J on J.ITEMUPC=I.SKU
i do ponder if your conditons would bring back duplicate records may be ?? without DDL its hard to tell
***The first step is always the hardest *******
July 12, 2012 at 12:31 pm
Do you intend to join SKU to both SKU and UPC? I would think the second join would be to a UPC column, not to the same SKU column. Might make sense in your database, but it seems like it might be a typo.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 12, 2012 at 2:32 pm
In this case SKU and UPC are the same and therefore its usage is correct. However my challenge is that there may be more than 1 record that meets this condition for one particular UPC. Therefore the script is returning more records than expected. How can I fix it so that it only returns one record per UPC?
July 12, 2012 at 3:07 pm
try the union that i added
***The first step is always the hardest *******
July 13, 2012 at 12:58 am
JayWinter (7/12/2012)
Is the statement below correct?I.SKU will be used to retrieve a record from from ITEMMAST when either J.SKU=I.SKU or J.ITEMUPC=I.SKU
FROM
Evy_RH_Objects.dbo.RETAIL_SALES I
INNER JOIN RH2007_EvyLive.dbo.ITEMMAST J on J.SKU=I.SKU or J.ITEMUPC=I.SKU
You need to know a little about the distribution of values in the SKU and ITEMUPC columns of ITEMMAST. Here's a little sample to show you what I mean:
SET NOCOUNT ON
DROP TABLE #RETAIL_SALES
SELECT *
INTO #RETAIL_SALES
FROM (
SELECT ID_RETAIL_SALES = 1, SKU = 234 UNION ALL
SELECT 2, 657 UNION ALL
SELECT 3, 873 UNION ALL
SELECT 4, 396 UNION ALL
SELECT 5, 172 UNION ALL
SELECT 6, 852 ) d
DROP TABLE #ITEMMAST
SELECT *
INTO #ITEMMAST
FROM (
SELECT ID_ITEMMAST = 10, SKU = 234, ITEMUPC = NULL UNION ALL
SELECT 20, 657, NULL UNION ALL
SELECT 30, NULL, 873 UNION ALL
SELECT 40, 396,396 UNION ALL
SELECT 50, 172, 396 UNION ALL
SELECT 60, 172, 852) d
SELECT *
FROM #RETAIL_SALES i
INNER JOIN #ITEMMAST j ON i.SKU IN(j.SKU,j.ITEMUPC)
ORDER BY i.ID_RETAIL_SALES
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
July 13, 2012 at 1:00 am
JayWinter (7/12/2012)
In this case SKU and UPC are the same and therefore its usage is correct. However my challenge is that there may be more than 1 record that meets this condition for one particular UPC. Therefore the script is returning more records than expected. How can I fix it so that it only returns one record per UPC?
Can you provide some sample data and the query you are using?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
July 13, 2012 at 4:42 pm
JayWinter (7/12/2012)
In this case SKU and UPC are the same and therefore its usage is correct. However my challenge is that there may be more than 1 record that meets this condition for one particular UPC. Therefore the script is returning more records than expected. How can I fix it so that it only returns one record per UPC?
I wouldn't recommend UNION except as a last resort since that will force two scans of the table.
Assuming the SKUs can't be NULL, try code below. If they can be NULL, naturally the "<>" has to be changed to handle that.
SELECT
...
FROM Evy_RH_Objects.dbo.RETAIL_SALES I
INNER JOIN RH2007_EvyLive.dbo.ITEMMAST J ON
(i.SKU = j.SKU) OR
(i.SKU <> j.SKU AND i.SKU = j.ITEMUPC)
ORDER BY
i.ID_RETAIL_SALES
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply