Result that matches all the values from a list

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 2005 Forums

Transact-SQL (2005)

Result that matches all the values from a list

Author

Topic

chorofonfilo
Starting Member

40 Posts

Posted - 2011-04-08 : 13:16:10

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...:)

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2011-04-08 : 13:23:12

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

______________________

chorofonfilo
Starting Member

40 Posts

Posted - 2011-04-08 : 13:55:10

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...:)

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2011-04-08 : 13:58:23

Try it:


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

______________________

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-08 : 14:00:12

SELECT I.IdProduct
FROM tbPropertyProduct I
WHERE I.IdProperty IN(20,23)
GROUP BY I.IdProduct
HAVING COUNT(DISTINCT I.IdProperty)=2

chorofonfilo
Starting Member

40 Posts

Posted - 2011-04-08 : 14:07:52

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

Perseverance worths it...:)

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2011-04-08 : 14:12:56

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;

______________________

chorofonfilo
Starting Member

40 Posts

Posted - 2011-04-08 : 14:16:27

Fantastic thanks a lot!.

Perseverance worths it...:)

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2011-04-08 : 14:16:49

you are welcome

______________________

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-04-08 : 16:04:58

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/

chorofonfilo
Starting Member

40 Posts

Posted - 2011-04-08 : 16:34:36

Thanks Mirko!, this seemed to be an interesting question for all of you I am glad I have gotten many responses :).

Perseverance worths it...:)

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2011-04-09 : 01:23:48

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.

______________________

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-04-09 : 09:49:06

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/

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2011-04-09 : 11:53:12

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.

______________________

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-04-09 : 12:08:14

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/

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-04-10 : 01:31:25

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/

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-04-10 : 12:53:15

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:[url]http://mirko-marovic-eng.blogspot.com/2011/04/rows-matching-all-values-from-list.html[/url]

Mirko

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources