[SQL] Selecting item from table, whether or not it has corresponding entry in 2nd table...

Discussion in 'ASP General' started by Augustus, Feb 4, 2004.

  1. Augustus

    Augustus Guest

    Hiya!

    I have a question:

    I have 2 tables... "Inventory" and "Price"

    These tables are used by 2 restaurants... the inventory has what they sell,
    and prices has the corresponding prices to those items for each location.

    What I want to do:
    I want to get a list of all items in the "Inventory" table and the
    corresponding price from the "Price" table for a location, unless the column
    "SHOW" (in the "Price" table) is set to "N"

    It might sound like "Just use a LEFT JOIN"... but if there is no entry for
    the item in the "Price" table, I still want a result

    I tried using:
    ===============
    select i.name, p.price1
    from inventory i
    LEFT JOIN prices p
    ON p.itemID=i.itemID
    where (
    (i.itemType=7)
    AND (i.canusa='usa')
    AND (p.storeID=99)
    AND (p.show='y')
    )

    ===============

    But that only returns the items that have prices set up in the prices
    table...

    So I tried adding
    ===============
    OR (NOT EXISTS (SELECT * FROM prices where (p.itemID=i.itemID) AND
    (i.itemType=7))
    )
    ===============

    to the end of the WHERE conditions, but this returns everything in the
    inventory table

    I then tried changing the whole statement to:

    ===============
    select i.name, p.price1
    from inventory i
    LEFT JOIN prices p
    ON p.itemID=i.itemID
    where (
    (i.itemType=7)
    AND (i.canusa='usa')
    AND (p.storeID=99)
    AND (p.show='y')
    )
    OR (i.itemType=7)
    AND (i.canusa='usa')
    AND (NOT EXISTS (SELECT * FROM prices where (p.itemID=i.itemID) AND
    (i.itemType=7)))
    ===============

    Now that does work... BUT, I am repeating some of the conditions of the
    WHERE statement: (i.itemType=7) AND (i.canusa='usa')
    And I am wondering if this is the most efficient way of doing it, or if its
    just some bandaid approach that works and the true logic has escaped me?
    (and is the "not exists" condition going to give some extra load the server
    or not)

    Any help is appreciated

    Thanks,

    Clint
    Augustus, Feb 4, 2004
    #1
    1. Advertising

  2. "Augustus" <> wrote in message
    news:bvrtci$10f5pa$-berlin.de...
    > Hiya!
    >
    > I have a question:
    >
    > I have 2 tables... "Inventory" and "Price"
    >
    > These tables are used by 2 restaurants... the inventory has what they

    sell,
    > and prices has the corresponding prices to those items for each

    location.
    >
    > What I want to do:
    > I want to get a list of all items in the "Inventory" table and the
    > corresponding price from the "Price" table for a location, unless the

    column
    > "SHOW" (in the "Price" table) is set to "N"
    >
    > It might sound like "Just use a LEFT JOIN"... but if there is no entry

    for
    > the item in the "Price" table, I still want a result
    >
    > I tried using:
    > ===============
    > select i.name, p.price1
    > from inventory i
    > LEFT JOIN prices p
    > ON p.itemID=i.itemID
    > where (
    > (i.itemType=7)
    > AND (i.canusa='usa')
    > AND (p.storeID=99)
    > AND (p.show='y')
    > )
    >
    > ===============
    >
    > But that only returns the items that have prices set up in the prices
    > table...
    >
    > So I tried adding
    > ===============
    > OR (NOT EXISTS (SELECT * FROM prices where (p.itemID=i.itemID) AND
    > (i.itemType=7))
    > )
    > ===============
    >
    > to the end of the WHERE conditions, but this returns everything in the
    > inventory table
    >
    > I then tried changing the whole statement to:
    >
    > ===============
    > select i.name, p.price1
    > from inventory i
    > LEFT JOIN prices p
    > ON p.itemID=i.itemID
    > where (
    > (i.itemType=7)
    > AND (i.canusa='usa')
    > AND (p.storeID=99)
    > AND (p.show='y')
    > )
    > OR (i.itemType=7)
    > AND (i.canusa='usa')
    > AND (NOT EXISTS (SELECT * FROM prices where (p.itemID=i.itemID) AND
    > (i.itemType=7)))
    > ===============
    >
    > Now that does work... BUT, I am repeating some of the conditions of

    the
    > WHERE statement: (i.itemType=7) AND (i.canusa='usa')
    > And I am wondering if this is the most efficient way of doing it, or

    if its
    > just some bandaid approach that works and the true logic has escaped

    me?
    > (and is the "not exists" condition going to give some extra load the

    server
    > or not)
    >
    > Any help is appreciated
    >
    > Thanks,
    >
    > Clint


    Answered in m.p.i.asp.db. Please don't multi-post:
    http://aspfaq.com/5003
    Chris Hohmann, Feb 5, 2004
    #2
    1. Advertising

  3. Augustus

    Augustus Guest

    "Chris Hohmann" <> wrote in message
    news:%...
    > "Augustus" <> wrote in message
    > news:bvrtci$10f5pa$-berlin.de...
    >
    > Answered in m.p.i.asp.db. Please don't multi-post:
    > http://aspfaq.com/5003


    Thanks Chris

    I originally meant to crosspost to the 2 groups... it fit in the other one
    better than this one, but this one sees alot more activity and has answered
    most of my SQL questions pretty quickly...

    Clint
    Augustus, Feb 5, 2004
    #3
  4. Augustus

    Ken Fine Guest

    Clint:

    Be advised that people here hate it when you crosspost, even in
    meaningful/relevant groups. It burns folks up to spend a lot of time
    answering a question that's already been answered elsewhere.

    We've all done it, so it isn't a capital crime, but it's probably best to
    observe people's sensitivities in this matter.

    -KF

    "Augustus" <> wrote in message
    news:bvs589$u3ei2$-berlin.de...
    >
    > "Chris Hohmann" <> wrote in message
    > news:%...
    > > "Augustus" <> wrote in message
    > > news:bvrtci$10f5pa$-berlin.de...
    > >
    > > Answered in m.p.i.asp.db. Please don't multi-post:
    > > http://aspfaq.com/5003

    >
    > Thanks Chris
    >
    > I originally meant to crosspost to the 2 groups... it fit in the other one
    > better than this one, but this one sees alot more activity and has

    answered
    > most of my SQL questions pretty quickly...
    >
    > Clint
    >
    >
    Ken Fine, Feb 5, 2004
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Jim in Arizona
    Replies:
    4
    Views:
    7,898
    Jim in Arizona
    Aug 24, 2005
  2. AtomicBob
    Replies:
    14
    Views:
    847
    Toby Inkster
    May 2, 2006
  3. Martin Jay
    Replies:
    6
    Views:
    423
    Neredbojias
    May 15, 2006
  4. CJM
    Replies:
    2
    Views:
    197
  5. Vinod
    Replies:
    4
    Views:
    1,003
    Patrice
    Feb 24, 2005
Loading...

Share This Page