CODEWITHSUNDEEP
mysqlsearchleft-join
Esmaeil Soomari
Esmaeil Soomari

Reputation: 150

mysql query search in left join that have condition related to a column

I have two tables (A & B):
table A: 

+----+--------+
| id | title  |      
+----+--------+
|  1 | white  | 
|  2 | blue   | 
|  3 | red    | 
|  4 | white  |
|  5 | blue   | 
+----+--------+

table B:

+----+---------+------+------+
| id | content | A_id | type | 
+----+---------+------+------+
|  1 | dog     |    1 | good |
|  2 | dog     |    1 | bad  | 
|  3 | cat     |    2 | good | 
|  4 | cat     |    2 | bad  | 
|  4 | cat     |    2 | ugly | 
|  6 | crow    |    3 | good | 
|  7 | crow    |    3 | bad  |  
|  8 | crow    |    3 | ugly |  
|  9 | mouse   |    2 | good |  
| 10 | zebra   |    3 | bad  |  
|    |         |      |      | 
+----+---------+------+------+

result for this query:

SELECT A.*, B.content, B.type FROM A
LEFT JOIN B ON A.id=B.A_id
WHERE B.content='dog' OR B.content='cat' OR B.content='crow'
ORDER BY A.id ASC;

would be:

+----+-------+---------+------+
| id | title | content | type | 
+----+-------+---------+------+
|  1 | white | dog     | good |  
|  1 | white | dog     | bad  | 
|  2 | blue  | cat     | good | 
|  2 | blue  | cat     | bad  | 
|  2 | blue  | cat     | ugly |  
|  3 | red   | crow    | good | 
|  3 | red   | crow    | bad  | 
|  3 | red   | crow    | ugly | 
+----+-------+---------+------+

This resut for:

id=1 has two types:

good and bad but for

id=2 and id=3 has three types:

good, bad and ugly.

instead of above result I want a query that only give that ids that have common types in id=1 and id=2 and id=3. that means if for id=1 results types are good and bad for id=2 and 3 the rows that have type=ugly should be neglected. I Want a query that give me this result:

+----+-------+---------+------+
| id | title | content | type | 
+----+-------+---------+------+
|  1 | white | dog     | good |  
|  1 | white | dog     | bad  | 
|  2 | blue  | cat     | good | 
|  2 | blue  | cat     | bad  |   
|  3 | red   | crow    | good | 
|  3 | red   | crow    | bad  | 
+----+-------+---------+------+

Upvotes: 0

Views: 77

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

A bit hackish way for what you are trying to achieve

select A.*,B.*
from B
cross join(
  select 
  group_concat(type) all_types,
  count(distinct type) type_count
  from B
  where B.content IN('dog','cat','crow')
  group by content, A_id
  order by type_count
  limit 1
) B1
join A on A.id = B.A_id
where B.content IN('dog','cat','crow')
and find_in_set(b.type,B1.all_types) >0

Demo

First inner query will get the lowest distinct types as comma separated list using group_concat that matched in table B by using limit 1 to pick the lowest set.

In outer query used that type set from previous inner query to include only rows that has these types only

Upvotes: 1

Related Questions