JOINS in MYSQL and missing rows

Question

I have a query like this one :

SELECT DISTINCT 
      `a`.*,
      `b`.*,
      `c`.*      
FROM `a`     
INNER JOIN `b` ON (`b`.`a_id` = `a`.`id` )     
INNER JOIN `c` ON (`c`.`id` = `b`.`c_id`)       
WHERE (
  (`a`.`id` = 12345) AND
  (`b`.`foo`= "bar")
)

Basically, this takes the row from a with id=12345, and the rows from b that concern this row, as well as the rows from c about this b rows, only of the rows in b have foo=bar

Right now, if there is no b row that has foo=bar, the a row is not even returned. This is wrong. I want that no matter what the matching a row is returned (whether there are bs and cs). How can I do this? (is there a way?)

Answer 1

I think you should look up how to do OUTER JOINS. It sounds like that might be what you are asking for.

Something like:

SELECT DISTINCT 
  `a`.*,
  `b`.*,
  `c`.*
FROM `a` 
LEFT OUTER JOIN 
  `b` ON `b`.`a_id` = `a`.`id`
LEFT OUTER JOIN  
  `c` ON `c`.`id` = `b`.`c_id`
WHERE 
  `a`.`id` = 12345

EDIT: with new information (a condition on b, which has to have a column foo=bar. This works when there is no row in b at all. but if there is one with foo=notbar, I want it to return a as well), try this. Note: I removed tics to make it easier to read and see quotes.

SELECT DISTINCT 
  a.*,
  b.*,
  c.*
FROM a 
LEFT OUTER JOIN 
  b ON b.a_id = a.id and b.foo = 'bar'
LEFT OUTER JOIN  
  c ON c.id = b.c_id
WHERE 
  a.id = 12345

Answer 2

As explained by MJB your looking for an OUTER JOIN.

Joins Tuturial

Answer 3

As MJB said, you need to use an outer join so rows of a are included when they have no matching row in b.

If you want to restrict the matching rows of b to those that match another condition, you need to put those conditions into the JOIN expression for b. If you leave conditions for b in the WHERE clause, this excludes joined rows where b columns are NULL because of the outer join.

SELECT DISTINCT 
      `a`.*,
      `b`.*,
      `c`.*      
FROM `a`     
LEFT OUTER JOIN (`b` INNER JOIN `c` ON (`c`.`id` = `b`.`c_id`))
  ON (`b`.`a_id` = `a`.`id` AND `b`.`foo` = 'bar')     
WHERE `a`.`id` = 12345

Notice I used parentheses to join c to b before evaluating the outer join to a.

Also I recommend using single-quotes for string literals, not double-quotes. Just to get into the habit of using standard SQL where possible, instead of MySQL-specific syntax.