Where clause within join for MySQL?

Not quite sure how to tackle this one as my knowledge is limited.

However - I had three tables.

A category table.

category_id, category_name
1          | ABC
2          | DEF
3          | GHI

A links table.

link_id, category_id, link_name
1      | 1          | Bla
2      | 1          | Bla bla
3      | 2          | Bla Bla Bla

A details table

details_id, link_id, details_status (then some more irrelevant fields)
1         | 1      | 0
2         | 1      | 1
3         | 1      | 0
4         | 2      | 1
5         | 3      | 1

The status field is either 0, 1 or 2.

What I would like to do is select a list of links for a given category. Simple enough.

What I would then like to do is join the details table so I can highlight my link if a given status is found.

For example within category 'ABC' details_id's 1 and 3 are status 0, when running a query returning the list of links for category 'ABC'.

My query would return something like a count of开发者_如何学C status where details_status=0

So for category_id 1 I'd like to get: link_id, link_name, status_count

Yielding:

1 | bla         | 2
2 | bla bla     | 0
3 | bla bla bla | 0

select links.link_id, links.link_name, 
       sum(if(details.details_status=0,1,0)) as status_count
from links 
inner join category on links.category_id = category.category_id
inner join details on details.link_id = links.link_id
where category.category_name = 'ABC'
group by links.link_id, links.link_name

The IF statement contains 3 parameters. The first is a logical expression that is evaluated. The second is the return value if that expression is true. The third is the return value if that expression is false. So, here, we are checking to see if details_status = 0. If it is, we want to return a 1, otherwise 0. Then, we wrap that in the SUM function to get the totals we're looking for.

Sounds like the category tableis not relevant to your question.

To achieve your requested output, try this:

select l.link_id, link_name, count(d.link_id)
from links l
left join details d on d.link_id = l.link_id and details_status = 0
group by 1, 2;

The key to making this work is putting the details_status = 0 into the on clause, not the where clause.

You could join this query up with the category table easily enough if you wanted to.