FROM clause challenge

  • 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

  • Looks good to me. The real test is does the query return what you expect?

  • 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.

  • 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 *******

  • 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

  • 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?

  • try the union that i added

    ***The first step is always the hardest *******

  • 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


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • 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?


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • 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