CODEWITHSUNDEEP
mysqlsql
mceran
mceran

Reputation: 3

Is the sql query correct?

I tried to question the Product criteria in the table. The NAME and VAL values are in the different rows. I question whether the records are equal to NAME = aa, bb, cc,. and VAL = 10,20,30. I'm turning results. I think the SQL query is working properly. I was able to correct query the columns with the following method.

But I think it's wrong for performance. Is this SQL query proper from performance aspect? If not, can you give an example for the correct SQL query? Thank you for your help.

test table:

Id  PID NAME VAL
1   1   aa   10
2   1   bb   20
3   1   cc   30

4   2   aa   10
5   2   bb   20
6   2   cc   30

7   3   aa   10
8   3   bb   20
9   3   cc   999



Query:

SELECT PID from test WHERE
PID IN (SELECT PID FROM test WHERE NAME='aa' and VAL='10')
AND
PID IN (SELECT PID FROM test WHERE NAME='bb' and VAL='20')
AND
PID IN (SELECT PID FROM test WHERE NAME='cc' and VAL='30')
GROUP BY (PID)



Result:

1
2

I'm thinking of using it with such (<> =) operators. This was the final result. I want you to comment if it's true.

SELECT t.pid from test t 
WHERE
(t.name = 'aa' and t.val = 10)
OR 
(t.name = 'bb' and t.val = 20) 
OR 
(t.name = 'cc' and t.val > 30) 
GROUP BY t.pid 
HAVING count(t.pid) = 3;

Upvotes: 0

Views: 113

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

I recommend aggregation for this purpose. Assuming you don't have name/val duplicates, then:

select t.pid
from test t
where (t.name, t.val) in ( ('aa', '10'), ('bb', '20'), ('cc', '30') )
group by t.pid
having count(*) = 3;

If you can have duplicates, then use:

having count(distinct t.name, t.val)

Upvotes: 1

Yidna
Yidna

Reputation: 427

You can also write

SELECT a.PID
FROM   test a, test b, test c
WHERE  (a.NAME = 'aa' AND a.VAL = '10') AND
       (b.NAME = 'bb' AND b.VAL = '20') AND
       (c.NAME = 'cc' AND c.VAL = '30') AND
       (a.PID = b.PID AND b.PID = c.PID);

I'm not too sure about the performance as the query optimizer might reduce both of these into the same query, but it should not perform worse than your current one.

Upvotes: 1

FanoFN
FanoFN

Reputation: 7114

What your query doing is basically similar to this:

SELECT PID 
FROM test 
WHERE NAME IN ('aa','bb','cc') 
AND VAL IN ('10','20','30')
GROUP BY PID;

Unless, you have other condition you would like to add.

Upvotes: 0

Related Questions