By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,760 Members | 955 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,760 IT Pros & Developers. It's quick & easy.

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

P: n/a
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


Jul 19 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
"Augustus" <Im*************@Rome.com> wrote in message
news:bv*************@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


Answered in m.p.i.asp.db. Please don't multi-post:
http://aspfaq.com/5003
Jul 19 '05 #2

P: n/a

"Chris Hohmann" <no****@thankyou.com> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...
"Augustus" <Im*************@Rome.com> wrote in message
news:bv*************@ID-97594.news.uni-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
Jul 19 '05 #3

P: n/a
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" <Im*************@Rome.com> wrote in message
news:bv************@ID-97594.news.uni-berlin.de...

"Chris Hohmann" <no****@thankyou.com> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...
"Augustus" <Im*************@Rome.com> wrote in message
news:bv*************@ID-97594.news.uni-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

Jul 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.