合并SELECT查询和SELECT COUNT查询

I have a table (associations) in MySql with unique and duplicate keys:

id | key
 1 | a
 2 | b
 3 | b
 4 | c
 5 | d
 6 | d

And I have a table (products) with products:

id | product  | type
 1 | product1 | one
 2 | product2 | one
 3 | product3 | two
 4 | product4 | two
 5 | product5 | two
 6 | product6 | two

Now I only what the products that are unique in associations and type 'two' so I get those ID's by this query:

SELECT assoc.id, count(*)
FROM __associations assoc
GROUP BY assoc.key
HAVING COUNT(*) <= 1

Which returns ID 1 and 4 and

SELECT prod.id, prod.product, prod.type
FROM __products prod
WHERE prod.type = two

which return 3,4,5 and 6

But my attempts to combine these two queries and get ID 4 all fails :(

This Query should give you the correct result:

SELECT prod.id, prod.product, prod.type
FROM __products prod
WHERE prod.type = two and prod.id in (SELECT assoc.id    
  FROM __associations assoc
  GROUP BY assoc.key
  HAVING COUNT(*) <= 1)

Try this:

SELECT assoc.id, count(*)
FROM __associations assoc
JOIN __products prod ON assoc.id = prod.id
GROUP BY assoc.key
HAVING COUNT(*) <= 1 AND 
       COUNT(CASE WHEN prod.type = 'two' THEN 1 END) > 0

The query uses a conditional aggregate in its HAVING clause, in order to filter out assoc.key groups not being related to at least one products record with type = 'two'.

You need to select from multiple tables.

SELECT table1.column1, table2.column2 FROM table1, table2 WHERE table1.column1 = table2.column1;

SQL basics: Query multiple tables

SELECT assoc.id, count(*), prod.product, prod.type FROM __associations assoc, __products prod´ WHERE prod.id = assoc.id AND prod.type = two GROUP BY assoc.key HAVING COUNT(*) <= 1