CODEWITHSUNDEEP
mysqljoinsubquery
A. Hart
A. Hart

Reputation: 1

Join two subqueries aliases

I have already found many similar requests here, but unfortunately nothing was the same. I try to link two subqueries, but somehow it does not work. Here is my code:

SELECT a_id, a_sid, a_pid, a_trait1, a_trait2, a_trait3 FROM 
(SELECT 
id AS a_id, sid AS a_sid, pid AS a_pid, max(trait1) AS a_trait1, trait2 AS a_trait2, trait3 AS a_trait3 
FROM 
table_a 
GROUP BY 
sid) AS derived_a, 
(SELECT 
id AS s_id, trait10 AS s_trait10, trait11 AS s_trait11, trait12 AS s_trait12 
FROM 
table_s) AS derived_s 
LEFT JOIN 
derived_a ON derived_a.a_sid = derived_s.s_id

Upvotes: 0

Views: 596

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133370

wrong sintax you should use the left join for the subquery and not join the alias 

  SELECT a_id, a_sid, a_pid, a_trait1, a_trait2, a_trait3 
  FROM  ( 
      SELECT  id AS a_id, sid AS a_sid, pid AS a_pid
        , max(trait1) AS a_trait1, trait2 AS a_trait2, trait3 AS a_trait3 
      FROM  table_a 
      GROUP BY id
  ) AS derived_a 
  left JOIN  (
      SELECT id AS s_id, trait10 AS s_trait10
        , trait11 AS s_trait11, trait12 AS s_trait12 
      FROM table_s) AS derived_s  ON derived_a.a_sid = derived_s.s_id

Upvotes: 2

Related Questions