CODEWITHSUNDEEP
mysqlsql
doe j
doe j

Reputation: 23

EXISTS number of rows

SELECT * FROM table1 WHERE EXISTS (SELECT * FROM table2 WHERE *condition*)

Can I check if there are, for example, 3 rows respecting the table2 condition? So something like 

[...]WHERE EXISTS 3 (SELECT[...]

Thanks

Upvotes: 1

Views: 121

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

According to your question to Gordon's answer, you want to select records the ID of which occurs three times as an AssociatedID. So select all AssociatedIDs that occur thrice and then select all records with an ID n that data set.

select *
from mytable 
where id in (select associatedid from mytable group by associatedid having count(*) = 3);

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

You can simply count:

SELECT * FROM table1 WHERE (SELECT COUNT(*) FROM table2 WHERE *condition*) = 3;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269803

You would do this with something other than exists. For instance, aggregate table2 and count the rows:

SELECT *
FROM table1 t1 JOIN
     (SELECT t2.??
      FROM table2 t2
      WHERE conditions (no correlation conditions)
      GROUP BY t2.??
      HAVING COUNT(*) >= 3
     ) t2
     ON t1.?? = t2.??;

Upvotes: 3

Related Questions