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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Result that matches all the values from a list
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

chorofonfilo
Starting Member

40 Posts

Posted - 04/08/2011 :  13:16:10  Show Profile  Send chorofonfilo a Yahoo! Message  Reply with Quote
Hello and thanks for your help in advance.

I have a table that looks like this:

IdProduct | IdProperty
19 | 19
19 | 23
20 | 20
20 | 23

Basically I would like to get the IdProduct that matches the IdProperty numbers 20 and 23

I have tried this:

Select I.IdProduct from tbPropertyProduct I
where I.IdProperty=20 and I.IdProperty=23

And is not working...

I would appreciate any suggestion you guys could give me.

Thank you.

Perseverance worths it...:)

Edited by - chorofonfilo on 04/08/2011 13:18:12

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 04/08/2011 :  13:23:12  Show Profile  Reply with Quote
Select I.IdProduct from tbPropertyProduct I
where I.IdProduct=20 and I.IdProperty=23

______________________
Go to Top of Page

chorofonfilo
Starting Member

40 Posts

Posted - 04/08/2011 :  13:55:10  Show Profile  Send chorofonfilo a Yahoo! Message  Reply with Quote
quote:
Originally posted by ms65g

Select I.IdProduct from tbPropertyProduct I
where I.IdProduct=20 and I.IdProperty=23

______________________




Thanks ms65g but in this case I must retrieve the IdProduct that has both properties so I cant use this Id explicitly in the where clause.

Any other ideas?.

Perseverance worths it...:)
Go to Top of Page

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 04/08/2011 :  13:58:23  Show Profile  Reply with Quote
Try it:

SELECT IdProduct
  FROM Table
 WHERE IdProperty IN (20, 23)
 GROUP BY IdProduct
HAVING COUNR(DISTINCT IdProperty) = 2;


______________________
Go to Top of Page

robvolk
Most Valuable Yak

USA
15732 Posts

Posted - 04/08/2011 :  14:00:12  Show Profile  Visit robvolk's Homepage  Reply with Quote
SELECT I.IdProduct
FROM tbPropertyProduct I
WHERE I.IdProperty IN(20,23)
GROUP BY I.IdProduct
HAVING COUNT(DISTINCT I.IdProperty)=2

Go to Top of Page

chorofonfilo
Starting Member

40 Posts

Posted - 04/08/2011 :  14:07:52  Show Profile  Send chorofonfilo a Yahoo! Message  Reply with Quote
Thank you guys, 2 is the number of Properties that i am including right?, in this case 20 and 23.

Perseverance worths it...:)

Edited by - chorofonfilo on 04/08/2011 14:08:16
Go to Top of Page

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 04/08/2011 :  14:12:56  Show Profile  Reply with Quote
quote:
Originally posted by chorofonfilo

Thank you guys, 2 is the number of Properties that i am including right?, in this case 20 and 23.

Perseverance worths it...:)



Yes.
Also two other method:

SELECT IdProduct
FROM tbPropertyProduct
GROUP BY IdProduct
HAVING COUNT(DISTINCT CASE WHEN IdProperty IN (20, 23) THEN IdProperty END) = 2;


Or:

SELECT IdProduct
  FROM tbPropertyProduct
 GROUP BY IdProduct
HAVING COUNT(CASE WHEN IdProperty = 20 THEN 1 END) > 0
   AND COUNT(CASE WHEN IdProperty = 23 THEN 1 END) > 0;


______________________
Go to Top of Page

chorofonfilo
Starting Member

40 Posts

Posted - 04/08/2011 :  14:16:27  Show Profile  Send chorofonfilo a Yahoo! Message  Reply with Quote
Fantastic thanks a lot!.

Perseverance worths it...:)
Go to Top of Page

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 04/08/2011 :  14:16:49  Show Profile  Reply with Quote
you are welcome

______________________
Go to Top of Page

mmarovic
Aged Yak Warrior

Czech Republic
518 Posts

Posted - 04/08/2011 :  16:04:58  Show Profile  Visit mmarovic's Homepage  Send mmarovic a Yahoo! Message  Reply with Quote
Well, there is another, the most efficient one:
select p1.IdProduct
  from tbPropertyProduct p1
  join tbPropertyProduct p2 on p1.idProduct = p2.idProduct and p2.idProperty = 23
  where p1.idProperty = 20


Mirko

My blog: http://mirko-marovic-eng.blogspot.com/
Go to Top of Page

chorofonfilo
Starting Member

40 Posts

Posted - 04/08/2011 :  16:34:36  Show Profile  Send chorofonfilo a Yahoo! Message  Reply with Quote
Thanks Mirko!, this seemed to be an interesting question for all of you I am glad I have gotten many responses :).

Perseverance worths it...:)
Go to Top of Page

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 04/09/2011 :  01:23:48  Show Profile  Reply with Quote
quote:
Originally posted by mmarovic

Well, there is another, the most efficient one:
select p1.IdProduct
  from tbPropertyProduct p1
  join tbPropertyProduct p2 on p1.idProduct = p2.idProduct and p2.idProperty = 23
  where p1.idProperty = 20


Mirko

My blog: http://mirko-marovic-eng.blogspot.com/


Are you sure it is most efficient?!
Self join is more efficient than single table source?!
Also when OP needs to find Products that match with 10 values, then your query will be very spectacular.

______________________

Edited by - ms65g on 04/09/2011 01:31:48
Go to Top of Page

mmarovic
Aged Yak Warrior

Czech Republic
518 Posts

Posted - 04/09/2011 :  09:49:06  Show Profile  Visit mmarovic's Homepage  Send mmarovic a Yahoo! Message  Reply with Quote
quote:
Originally posted by ms65g

quote:
Originally posted by mmarovic

Well, there is another, the most efficient one:
select p1.IdProduct
  from tbPropertyProduct p1
  join tbPropertyProduct p2 on p1.idProduct = p2.idProduct and p2.idProperty = 23
  where p1.idProperty = 20


Mirko

My blog: http://mirko-marovic-eng.blogspot.com/


Are you sure it is most efficient?!
Self join is more efficient than single table source?!
Also when OP needs to find Products that match with 10 values, then your query will be very spectacular.

______________________



The task is to find products matching two values, not ten. ProductId and property are most probably primary keys, most probably clustered. It depends on data distribution, but if data are relativly evennly distributed by propertyIds and propertyId is the first column in the index, this query would read small percent of all rows from the table. The query would have to read all values with one property id value and among these it would read rows with another property id for the same product.

Solution with group by always read all rows.

So even with ten values this approach is more efficient than query you posted.

The query Rob posted is much more efficient then yours because it reads only rows having property id 20 and 23.

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

Edited by - mmarovic on 04/09/2011 10:25:49
Go to Top of Page

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 04/09/2011 :  11:53:12  Show Profile  Reply with Quote
It’s possible for you create a sample data with some rows for instance and any indexed you would like to have then compared your query with other then show us your achieved results?
How you compare two queries for performance?
No problem, numbers of reads, execution time or estimated cost is valid parameters for comparing.

______________________
Go to Top of Page

mmarovic
Aged Yak Warrior

Czech Republic
518 Posts

Posted - 04/09/2011 :  12:08:14  Show Profile  Visit mmarovic's Homepage  Send mmarovic a Yahoo! Message  Reply with Quote
I don't have time for that. You are free to try it yourself.

To add to my previous post, if the key starts with idProduct, then Robs' query might be the most efficient under right data distribution.

His query would be more efficient even if the index starts with IdProperty if data distribution is right: not big enough number of rows or big number of rows but pretty even distribution of rows with Property ids 20 and 23.

Your query will always be slower then Rob's because it reads all rows. The only chance you have is when number of rows in the table is so small that the difference is not measurable.

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

Edited by - mmarovic on 04/09/2011 12:09:33
Go to Top of Page

mmarovic
Aged Yak Warrior

Czech Republic
518 Posts

Posted - 04/10/2011 :  01:31:25  Show Profile  Visit mmarovic's Homepage  Send mmarovic a Yahoo! Message  Reply with Quote
ms65q, I apologize, I overlooked that you offered more efficient solution at pretty much the same time as Rob. However, you then posted a couple less efficient solutions.

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

Edited by - mmarovic on 04/10/2011 01:34:12
Go to Top of Page

mmarovic
Aged Yak Warrior

Czech Republic
518 Posts

Posted - 04/10/2011 :  12:53:15  Show Profile  Visit mmarovic's Homepage  Send mmarovic a Yahoo! Message  Reply with Quote
I do not have time prepare test data and test different scenarios. However, the problem is interesting, so I put together performance considerations in the new blog post:http://mirko-marovic-eng.blogspot.com/2011/04/rows-matching-all-values-from-list.html

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

Edited by - mmarovic on 04/14/2011 12:25:43
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000