算一个非常复杂的选择mysql

Question

I have a very complex select that union 3 tables to show customer's last activities.

so:

select c.data, p.user, concat(c.user, ' buy your product') as logs from sell c inner join posts p on c.foto = p.id where p.user = ?
union all
select f.data, c.user, concat(c.user, ' asked you a question') as logs from questions f inner join cadastro c ON f.user = c.id where f.nick = ?
union all
select l.data, l.user, concat(l.user, ' like your product') as logs from likes l inner join posts p on l.post = p.id where p.user = ?
order by data desc

well, each of this tables has a row called SEEN. it is a varchar set with 0 or 1 (0 the user has not seen the message, 1 the user has already seen it).

What I want to add in this query is something to count the rows where seen = 0.

I want to show: YOU HAVE 4 new alerts.

select count(*) will not work and will not count right (the seen set to 1).

What can I do to count all this selects where seen = 0?

Answer 1

Try this

select a.*, SUM(IF(a.seen='0', 1, 0)) as 'unseen_count' FROM (
    select c.data, p.user, concat(c.user, ' buy your product') as logs, seen from sell c inner join posts p on c.foto = p.id where p.user = ?
    union all
    select f.data, c.user, concat(c.user, ' asked you a question') as logs, seen from questions f inner join cadastro c ON f.user = c.id where f.nick = ?
    union all
    select l.data, l.user, concat(l.user, ' like your product') as logs, seen from likes l inner join posts p on l.post = p.id where p.user = ?
) as a
order by data desc