CODEWITHSUNDEEP
mysql
Adonai
Adonai

Reputation: 73

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.

Upvotes: 0

Views: 19

Answers (1)

Blank
Blank

Reputation: 12378

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

Upvotes: 1

Related Questions