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

[Augustus]

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]

You need a left outer join.

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


"Augustus" <Imperial.Palace@Rome.com> wrote in message
news:bvropc$vpg8u$1@ID-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
>
>
>
>
>

[Chris Hohmann]

"Augustus" <Imperial.Palace@Rome.com> wrote in message
news:bvropc$vpg8u$1@ID-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