Complicated MySQL Query Problem

I have a problem with a MySQL Query: I have two tables: - clustercategories - domains

Now I have a SQL Query which lists all Domains of a specific category with category name - this is my Query:

SELECT domains.*, clustercategories.clustercategoryname
FROM (domains, clustercategories)
WHERE ((clustercategories.id = 3 AND (domains.cluster1id = 3 OR domains.cluster2id = 3)) 
OR (clustercategories.id = 10 AND (domains.cluster1id = 10 OR domains.cluster2id = 10))) 
AND domains.status = '1' 
GROUP BY domains.name 
ORDER BY domains.name

The Problem is now, that I also have a third table "subpages" where I want to count all entries of a specific domain with status = '1' and I don't know how to modify my query to work - I have tried this query, but I do not get any results:

SELECT domains.*, clustercategories.clustercategoryname
FROM (domains, clustercategories)
WHERE ((clustercategories.id = 3 AND (domains.cluster1id = 3 OR domains.cluster2id = 3) AND (SELECT COUNT(*) AS total FROM subpages WHERE subpages.domainid = domains.id AND subpages.status = '1')) 
OR (clustercategories.id = 10 AND (domains.cluster1id = 10 OR domains.cluster2id = 10) AND (SELECT COUNT(*) AS total FROM subpages WHERE subpages.domainid = domains.id AND subpages开发者_运维问答.status = '1'))) 
AND domains.status = '1' 
GROUP BY domains.name 
ORDER BY domains.name

Has anyone any ideas?

---

I think that you'll want to put your subquery into your projection, like this:

SELECT domains.*, clustercategories.clustercategoryname, 
(SELECT COUNT(*) FROM subpages WHERE subpages.domainid = domains.id AND subpages.status = '1') AS total
FROM domains, clustercategories
WHERE ((clustercategories.id = 3 AND (domains.cluster1id = 3 OR domains.cluster2id = 3)) 
OR (clustercategories.id = 10 AND (domains.cluster1id = 10 OR domains.cluster2id = 10))) 
AND domains.status = '1' 
GROUP BY domains.name 
ORDER BY domains.name

---

It looks to me your first query can be rewritten like this

SELECT  d.*
        , cc.clustercategoryname
FROM    domains d
        INNER JOIN clustercategories cc
          ON cc.id = d.cluster1id 
          OR cc.id = d.cluster2id
WHERE   cc.id IN (3, 10)
        AND d.status = '1'                                           
GROUP BY 
        d.name
ORDER BY 
        d.name

thus adding the count of subpages can be done like this

  SELECT  d.*
          , cc.clustercategoryname
          , sp.total
  FROM    domains d
          INNER JOIN clustercategories cc 
            ON cc.id = d.cluster1id 
               OR cc.id = d.cluster2id
          LEFT OUTER JOIN (
            SELECT  COUNT(*) AS total
                    , domainid
            FROM    subpages
            WHERE   subpages.status = '1'
            GROUP BY
                    domainid
          ) sp ON sp.domainid = d.domainid          
  WHERE   cc.id IN (3, 10)
          AND d.status = '1'                                           
  GROUP BY 
          d.name
  ORDER BY 
          d.name