Professional Web Applications Themes

[T-SQL] SELECTing item whether or not it has corresponding item in another table - ASP Database

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

  1. #1

    Default [T-SQL] SELECTing item whether or not it has corresponding item in another table

    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 Guest

  2. #2

    Default Re: [T-SQL] SELECTing item whether or not it has corresponding item in another table

    You need a left outer join.

    --
    Mark Schupp
    Head of Development
    Integrity eLearning
    [url]www.ielearning.com[/url]


    "Augustus" <Imperial.PalaceRome.com> wrote in message
    news:bvropc$vpg8u$1ID-97594.news.uni-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
    >
    >
    >
    >
    >

    Mark Schupp Guest

  3. #3

    Default Re: [T-SQL] SELECTing item whether or not it has corresponding item in another table

    "Augustus" <Imperial.PalaceRome.com> wrote in message
    news:bvropc$vpg8u$1ID-97594.news.uni-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
    SELECT
    i.name,
    p.price1
    FROM
    (
    SELECT
    name,
    itemID
    FROM
    inventory
    WHERE
    canusa = 'usa'
    ) AS i
    LEFT JOIN
    (
    SELECT
    price1,
    itemID
    FROM
    prices
    WHERE
    storeID = 99 AND
    show ='y'
    ) AS p
    ON
    i.itemID=p.itemID


    Chris Hohmann Guest

Similar Threads

  1. Selecting Item(s) on a DataGrid
    By Jaz Chana in forum Macromedia Flex General Discussion
    Replies: 3
    Last Post: May 9th, 07:51 PM
  2. Selecting Listbox item by data
    By FlashAsh99 in forum Macromedia Flash Actionscript
    Replies: 0
    Last Post: February 8th, 02:27 AM
  3. Selecting item in listbox by data
    By FlashAsh99 in forum Macromedia Flash Actionscript
    Replies: 0
    Last Post: February 8th, 02:26 AM
  4. Selecting row in datagrid by clicking everyway on the item
    By Liuc in forum ASP.NET Data Grid Control
    Replies: 4
    Last Post: November 14th, 08:11 AM
  5. Programmatically Selecting Item in Radio Button List
    By John Criswell in forum ASP.NET General
    Replies: 1
    Last Post: July 24th, 07:07 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139