Simple sql query question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Simple sql query question

Author

Topic

Stian
Starting Member

8 Posts

Posted - 2009-01-22 : 15:36:30

I got this code fragment:


SELECT Products.ProductName FROM Products
WHERE Products.ID IN (5, 7)

How can I make this not return anything if either ID 5 or 7 is not in the database?

I want the code to return rows only if there is a match for BOTH IDs in the table. If not, I want it to return nothing.

I managed to get this working using a temporary table or a subselect with HAVING, but the code is very messy.

Is there a simple way to make this work the way I want?

Thanks for any help,

Stian

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-22 : 15:46:55

SELECT Products.ProductName
FROM Products
WHERE Products.ID = 5 AND Products.ID = 7

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-22 : 15:47:28

IN uses OR, so your code is equivalent to:

SELECT Products.ProductName
FROM Products
WHERE Products.ID = 5 OR Products.ID = 7

Which isn't what you want, you want AND.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Stian
Starting Member

8 Posts

Posted - 2009-01-22 : 15:49:21

Hi tkizer and thanks for your reply.

I am afraid AND doesn´t work in this case.
You can´t have a row with both ID 5 AND 7, so the query will return nothing.

Stian

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-22 : 15:50:17

Oops, yes indeed you are right, that's what I get for being an autopilot when posting.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-22 : 15:51:30

Use this

select Products.ProductName  from Products where Products.ID in (5, 7)
and exists (select 1 from Products  where Products.ID =5)
and exists (select 1 from Products  where Products.ID =7)

---------OR

if exists(select 1 from Products where Products.ID =5)
begin 
	if exists(select 1 from Products where Products.ID =5)
	SELECT Products.ProductName FROM Products
	WHERE Products.ID IN (5, 7)
end

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-22 : 15:52:08

Don't use SELECT 1 when using EXISTS or NOT EXISTS. Use SELECT * for performance reasons.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Stian
Starting Member

8 Posts

Posted - 2009-01-22 : 15:53:08

quote:

Originally posted by sakets_2000

Use this

select Products.ProductName  from Products where Products.ID in (5, 7)
and exists (select 1 from Products  where Products.ID =5)
and exists (select 1 from Products  where Products.ID =7)

---------OR

if exists(select 1 from Products where Products.ID =5)
begin 
	if exists(select 1 from Products where Products.ID =5)
	SELECT Products.ProductName FROM Products
	WHERE Products.ID IN (5, 7)
end

Many thanks, sakets

I think the first alternative will work fine in my code - I´ll try it out when I get to work tomorrow.

Thanks for the fast replies guys!

Stian
Starting Member

8 Posts

Posted - 2009-01-22 : 15:55:50

quote:
Originally posted by tkizer

Don't use SELECT 1 when using EXISTS or NOT EXISTS. Use SELECT * for performance reasons.

Interesting! Will it be faster than SELECT TOP 1?

Stian

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-22 : 15:58:13

Yes. It's one of the rare cases where you should use SELECT *. The performance difference is very, very minor, but SELECT * is more efficient than anything else when using EXISTS/NOT EXISTS.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-22 : 16:02:58

quote:
Originally posted by tkizer

Yes. It's one of the rare cases where you should use SELECT *. The performance difference is very, very minor, but SELECT * is more efficient than anything else when using EXISTS/NOT EXISTS.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Thanks, I didn't know that.

Stian
Starting Member

8 Posts

Posted - 2009-01-23 : 03:39:38

Hey again guys - back at work trying out your suggestions...

The suggestion using Exists works on the example problem I presented, but I forgot to include another factor that complicates matters a tad more.

I have a customers and a products table. Between these two I have a table connecting them, signifying what products the customers have.

So the customers_products table contains two foreign key columns: CustomerID and ProductID. A customer can have several different products.

Lets say I want to list all customers that have both product 5 and product 7, and the customers_products table has these entries:

CustomerID 2 | ProductID 6
CustomerID 3 | ProductID 5
CustomerID 3 | ProductID 7
CustomerID 4 | ProductID 5
CustomerID 4 | ProductID 8

My query should return only the customer with ID 3 and none of the others.

Using the above suggested solution it would look like this:

select CustomerID from Customers_Products where ProductID in (5, 7)
and exists (select 1 from Products  where Products.ID =5)
and exists (select 1 from Products  where Products.ID =7)

The select would return both customer with ID 3 and 4 - since the exists will both be true even though only one customer has both products.

How can I make this work when bringing customer into the equation?

Stian

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-23 : 03:48:50

select CustomerID from Customers_Products where ProductID in (5, 7)
and exists (select 1 from Products where Products.ID =5)
and exists (select 1 from Products where Products.ID =7)
group by CustomerID
having count(CustomerID) >1

Stian
Starting Member

8 Posts

Posted - 2009-01-23 : 04:01:20

Brilliant - that did the trick!

Thanks a lot

Stian

quote:
Originally posted by bklr

select CustomerID from Customers_Products where ProductID in (5, 7)
and exists (select 1 from Products where Products.ID =5)
and exists (select 1 from Products where Products.ID =7)
group by CustomerID
having count(CustomerID) >1

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-23 : 04:02:47

welcome

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2009-01-23 : 14:56:48

quote:
Originally posted by bklr

select CustomerID from Customers_Products where ProductID in (5, 7)
and exists (select 1 from Products where Products.ID =5)
and exists (select 1 from Products where Products.ID =7)
group by CustomerID
having count(CustomerID) >1

I somehow don't think this will work for more data. If Customers_Products.ProductID is a foreign key to Products, Products will always have 5 and 7, thereby the exists will always equate to true. You simply happened to find one customer that had more than one row. What if the customer had product 5 twice, like the following:

CustomerID 2 | ProductID 6
CustomerID 3 | ProductID 5
CustomerID 3 | ProductID 5
CustomerID 4 | ProductID 5
CustomerID 4 | ProductID 8

You'd still return Customer 3 but that isn't exactly the request. Having DDL and more sample data would really help so that keys can be identied and multiple scenarios played out. Anyway, the OP wants customers that have both products, hence the following should do the trick for all his data.

select cp.CustomerID from Customers_Products cp
inner join Customers_Products cp2
on cp.CustomerId = cp2.CustomerId
where cp.ProductsId = 5 and cp2.ProductsId = 7

Terry

-- Procrastinate now!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-24 : 13:48:35

quote:
Originally posted by Stian

Brilliant - that did the trick!

Thanks a lot

Stian

quote:
Originally posted by bklr

select CustomerID from Customers_Products where ProductID in (5, 7)
and exists (select 1 from Products where Products.ID =5)
and exists (select 1 from Products where Products.ID =7)
group by CustomerID
having count(CustomerID) >1

wont this be enough?


select CustomerID from Customers_Products 
group by CustomerID 
HAVING COUNT(DISTINCT CASE WHEN ProductID IN (5,7) THEN ProductID ELSE NULL END) =2
AND COUNT(CASE WHEN ProductID NOT IN (5,7) THEN ProductID ELSE NULL END) =0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-24 : 13:50:38

quote:
Originally posted by bklr

select CustomerID from Customers_Products where ProductID in (5, 7)
and exists (select 1 from Products where Products.ID =5)
and exists (select 1 from Products where Products.ID =7)
group by CustomerID
having count(CustomerID) >1

add the below also to sample data and then check this

CustomerID 5 | ProductID 5
CustomerID 5 | ProductID 6
CustomerID 5 | ProductID 7
CustomerID 5 | ProductID 8

your code will still return ID 5 whereas according to OP this should not be returned.

Stian
Starting Member

8 Posts

Posted - 2009-01-25 : 14:58:38

Thanks for the further replies!

Seems I will have to make some more testing on this using the new suggestions. I´ll set up some tables with more test data to make sure I get it right.

Thanks again guys

Stian

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-26 : 08:47:44

ok...we will wait for updates on how you got on!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-26 : 08:56:21

[code]SELECT ProductName
FROM Products
WHERE ID IN (5, 7)
GROUP BY ProductName
HAVING COUNT(DISTINCT ID) = 2

SELECT ProductName
FROM Products
WHERE ID IN (5, 7)
HAVING MIN(ID) = 5
AND MAX(ID) = 7[/code]

E 12°55'05.63"
N 56°04'39.26"

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources