CODEWITHSUNDEEP
mysqlsqlsearchleft-join
Esmaeil Soomari
Esmaeil Soomari

Reputation: 150

mysql search in left join

I have two tables, A and B
table A has this columns:

id, title

table B has these columns:

id, content, A_id, type

In table B, A_id is foreign key related to table A. I want to write a search query that search title in A and left Join to B where A.id = B.A_id and search B.content

It is so easy:

SELECT A.*, B.content FROM A LEFT JOIN B ON A.id=B.A_id 
WHERE A.title like 'sometitle' AND B.content like 'somecontent';

But I have column 'type' in table B that have entries like this: 'good','bad','ugly','good','good'... when I search 'content' in table B I want that every content to be related to a 'type'. In other words, the query should give me result related to content1 and type good And content2 and type bad...
Example:
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  |  
|    |         |      |      | 
+----+---------+------+------+
I want a query that that its output be this:
+----+-------+---------+------+
| id | title | content | type | 
+----+-------+---------+------+
|  1 | white | dog     | good |   
|  2 | blue  | cat     | bad  | 
+----+-------+---------+------+

Upvotes: 1

Views: 2916

Answers (2)

Md. Suman Kabir
Md. Suman Kabir

Reputation: 5453

SQL HERE

Your query for the expected result was almost correct, just you used the wrong column content whether the correct column would be type. Look at the below query :

SELECT A.id, A.title, B.content, B.type FROM A 
LEFT JOIN B ON A.id=B.A_id 
WHERE (a.title='white' AND B.type='good')
OR (a.title='blue' AND B.type='bad') 
ORDER BY a.id asc

OUTPUT

+----+-------+----------+------+
| id | title | content  | type |
+----+-------+----------+------+
|  1 | white | content5 | good |
|  3 | blue  | content6 | bad  |
|  4 | white | content3 | good |
|  5 | blue  | content9 | bad  |
+----+-------+----------+------+

Upvotes: 3

Johannes
Johannes

Reputation: 67778

I think the query you posted at the end of your question should start like this:

SELECT A.id, A.title, B.A_id, B.content, B.type .... (etc.)

so that also B.A_id is fetched

Upvotes: 0

Related Questions