MySQL Join with COUNT(*)

i think today is n开发者_如何学Goot my day - sorry, but i have another COUNT(*) question:

I have this easy query:

SELECT * FROM domains 
LEFT JOIN subpages ON subpages.domainid = domains.id 
WHERE domains.id = 293 
AND subpages.seitenart = 'Startseite'

It works perfect but i need additionally a

SELECT COUNT(*) AS total FROM subpages WHERE subpages.statussub = '1' AND subpages.domainid = 293

Okay, for a better understanding: I have the table "domains" and the table "subpages". Now i want to display the Domaindetails of one Domain where i need the domain table and subpages table with field subpages.seitenart = "Startseite".

Additionally i have to count all subpages in the subpages table where subpages.statussub = '1'

Hope this is better explained!

Can anyone help pls?

Thanks, Sascha

---

If you dont want to limit the COUNT to seitenart = 'Startseite' try:

SELECT domains.*, COUNT(s1.<column_name>) as total
FROM subpages s1, domains LEFT JOIN subpages s2 ON s2.domainid = domains.id
WHERE domains.id = 293
AND s2.seitenart = 'Startseite'
AND s1.statussub = '1'
AND s1.domainid = domains.id