CODEWITHSUNDEEP
mysqljoin
user1760710
user1760710

Reputation:

Mysql : Left Join and Inner Join in a subquery

I have 3 tables that i have left joined but I would like to add a field from the table 3 which is inner joined with table 2. 

Table1
id
name
surname
table2_fk

Table2
id
entry_name
entry_code
table3_fk

Table3
id
type_name
type_desc

SELECT `Name`, `Type Description`
(SELECT
Table1.name AS `Name`,
Table1.surname AS `Surname`,
t2.entry_name AS `Entry`,
t2.entry_code AS `Entry Code`,
t3.type_name AS `Type Name`,
t3.type_desc AS `Type Description`

FROM Table1
LEFT JOIN table2 AS t2 ON Table1.table2_fk = t2.id
LEFT JOIN table3 AS t3 ON Table2.table3_fk = t3.id
) as subquery

My desired outcome is to have t2 inner join t3 and get field from table 1 and table 3

Based on my subquery I would like the Name and Type Description to show

Upvotes: 2

Views: 3482

Answers (2)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

If you want to INNER JOIN tables table2 and table3 and then LEFT JOIN to table1 then do this:

SELECT t1.name, t.type_desc AS `Type Description`
FROM Table1 AS t1
LEFT JOIN (
   SELECT t2.id, t3.type_desc
   FROM table2 AS t2 
   INNER JOIN table3 AS t3 ON t2.table3_fk = t3.id
) AS t ON t1.table2_fk = t2.id

But this could be perhaps expressed just as:

SELECT t1.name, t3.type_desc AS `Type Description`
FROM Table1 AS t1
LEFT JOIN table2 AS t2 ON t1.table2_fk = t2.id
LEFT JOIN table3 AS t3 ON t2.table3_fk = t3.id

Upvotes: 1

Chendur
Chendur

Reputation: 1159

I presume you are trying to left join table1 with the result of inner join between table2 and table3

select t1.name, t_res.type_desc from table1 t_1 
left join (
   select t_2.id, t_3.type_desc from table2 t_2 
   inner join table3 t_3 on t_2.table3_fk = t_3.id
) as t_res on t_1.table2_fk = t_2.id

Upvotes: 1

Related Questions