CODEWITHSUNDEEP
mysql
phpmeh
phpmeh

Reputation: 1792

Left Joins not Working with Subquery

I am trying to write this SQL:

SELECT  
   a.a_id as aid,  
   b.b_id as bid ,  
   c.title  
FROM b ,  
   ( SELECT  
       a.a_id
     FROM a  
     WHERE a.type = '2'  
   ) AS a  
LEFT JOIN c ON b.b_id = c.c_id

If I don't put a reference to the LEFT JOIN or the select c, then it works. If I do put the left join, then I get an error that the b.b_id is an unknown column. Why can't I use a left join here or more specifically, why is any column in table b unknown? The first part of the statement is a normal select from table b, so why can't it find those columns?

When do you use subqueries versus inner joins? In this example:

What I am trying to accomplish is to pull all categories of a certain type (table a), and also pull all topic titles (table c) where the category id is the same as what it is defined for the topic id (table b).

Upvotes: 0

Views: 177

Answers (2)

ron tornambe
ron tornambe

Reputation: 10780

If you interchange table "a" with "b" the sql compiles (but does this give the results you seek?):

SELECT  
   a.a_id as aid,  
   b.b_id as bid,  
   c.title  
FROM  
   ( SELECT  
       a.a_id
     FROM a  
     WHERE a.type = '2'  
   ) AS a, b  
LEFT JOIN c ON b.b_id = c.c_id

Upvotes: 1

Ravindra Gullapalli
Ravindra Gullapalli

Reputation: 9168

Try this

SELECT  
   a.a_id as aid,  
   b.b_id as bid ,  
   c.title  
FROM b INNER JOIN
   ( SELECT  
       a.a_id
     FROM a  
     WHERE a.type = '2'  
   ) AS a  
LEFT JOIN c ON b.b_id = c.c_id

Upvotes: 1

Related Questions