How to get values from tables connected thrugh a middle table, using mysql-CodePudding

Table A

a_id	a_name
1	apples
2	bananas
3	cherries
4	oranges

Table B

b_id	b_name
1	Hot
2	Cold

Table C

c_id	a_id	b_id
1	1	2
2	2	1
3	3	2
4	4	1
5	4	2

I am trying to get resulting table, which should show "a_name" and "b_name" with the following condition: where a_name like '%r%' and b_name like '%o%'.

Problem is that they should be found within the same row in "Table_C".

I've tried various methods of joining tables but I'm unable to get the desired result.

Here's my best coding attempt at this problem:

SELECT a.a_name, 
       b.b_name 
FROM Table_A a 
WHERE a.a_name LIKE '%r%' IN (SELECT a.a_id 
                              FROM Table_c 
                              WHERE b_id LIKE '%o');

Any help would be much appreciated.

CodePudding user response：

The problem with your query is that you're trying to extract values of "Table B" by using a filtering operation (WHERE clause). As long as the filtering clause "just filters" - reduces the number of rows of your table - , you can't access "b.b_name" inside your SELECT clause if the FROM contains only "Table A".

In this case you may want to use two JOIN operations in place of WHERE .. IN .. construct. The main table is "Table C" because it connects "Table A" and "Table B" together. Since you have two conditions to be applied on the names, in order to make this more efficient, you can filter on the two tables ("Table A" and "Table B") before applying the JOIN operation. In this way the DBMS will apply the matching on less rows.

SELECT A.a_name,
       B.b_name
FROM       tabC                                         C
INNER JOIN (SELECT * FROM tabA WHERE a_name LIKE '%r%') A
        ON C.a_id = A.a_id
INNER JOIN (SELECT * FROM tabB WHERE b_name LIKE '%o%') B
        ON C.b_id = B.b_id

Check the demo here.

CodePudding user response：

select 
   A.a_name,
   B.b_name
from TableA A
inner join TableC C on C.a_id = A.a_id
inner join tableB B on B.b_id = C.b_id
where A.a_name like '%r%' and B.b_name like '%o%';

The inner join 'glues' the table together on the condition specified after the ON
The where clause is a copy provided by you. I Just added the table aliases for clarity.

CodePudding user response：

Try below code:

select tableA.a_name, tableB.b_name 
from tableC 
left join tableA on tableA.a_id=tableC.a_id 
left join tableB on tableB.b_id=tableC.b_id
where tableA.a_name like '%r%' and tableB.b_name like '%o%'