Adonai Adonai - 5 months ago 8
MySQL Question

Show single set of results from two tables

I am trying to show the results of items from one table where the count from another table equals a number from the first. I have been stuck on how to go about doing this for a couple weeks now so iv finally decided to ask for help. Im having a hard time explaining exactly what it is i need but i will try my best.

I am using PDO to interact with my database which is mysql.

For instance i have two tables:

table 1
-----------------
key | name | total
1 | item 1 | 3
2 | item 2 | 4
3 | item 3 | 2

table 2
-----------------
key | table1 key
1 | 1
2 | 2
3 | 3
4 | 1
5 | 1
6 | 3
7 | 2
8 | 2


So in this case there would be 3/3 items for item 1, 3/4 items for item 2, and 2/2 items for item 3. So it would show item 1 and item 3 as a result because the count for those two equal the total from table one.

I hope I explained this well enough.

Answer

If you want a sql query to do that, try this:

select t1.*
from table1 t1
inner join (
    select table1_key, count(1) as cnt from table2 group by table1_key
) t2 on t1.key = t2.table1_key and t1.total = t2.cnt

SQLFiddel Demo