JOINS in MYSQL and missing rows
I have a query like this one :
SELECT DISTINCT
`a`.*,
`b`.*,
`c`.*
FROM开发者_StackOverflow社区 `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?)
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
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.
As explained by MJB your looking for an OUTER JOIN.
Joins Tuturial
精彩评论