I have the following tables:

TABLE: category

catid catfatherid catname
1         0  United States
2         1  Arizona 
3         1  Arkansas
4         1  California

and TABLE: ad

adid  catid      title
1   2         Arizona Item
2   3         Arkansas Item
3   4         California Item

I can join them without any problems and find the item I'm looking for, for example:

SELECT category.catid, category.catfatherid, category.catname, A.adid, A.title 
FROM ad A
LEFT JOIN category AS category ON A.catid = category.catid
where category.catname LIKE '%%Arizona%%'

However, I need to also be able to search by "United States" a开发者_运维知识库nd have all the entries in the ad table which have a catid that also has a catfatherid of that item show up as well.

Any ideas on how to do this?

Table SQL below...

CREATE TABLE `ad` (
  `adid` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `catid` int(11) NOT NULL,
  `title` varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY (`adid`)
);

INSERT INTO `ad` (`adid`, `catid`, `title`)
VALUES
 (1,2,'Arizona Item'),
 (2,3,'Arkansas Item'),
 (3,4,'California Item');

CREATE TABLE `category` (
  `catid` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `catfatherid` int(11) DEFAULT NULL,
  `catname` varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY (`catid`)
);

INSERT INTO `category` (`catid`, `catfatherid`, `catname`)
VALUES
 (1,0,'United States'),
 (2,1,'Arizona'),
 (3,1,'Arkansas'),
 (4,1,'California');

*EDIT I found a faster and much better way.. Requires one query..

To clarify what the request he had above: "However, I need to also be able to search by "United States" and have all the entries in the ad table which have a catid that also has a catfatherid of that item show up as well."

So both catid and catfatherid need to be looked at to find possible children.

SELECT search.catid search_father_id, search.catname search_father_name, 
         ad_cat.catid child_id, ad_cat.catname child_name, count(*) total 
        FROM ad 
              RIGHT JOIN category ad_cat ON ad_cat.catid = ad.catid,
                  category search
            WHERE search.catname LIKE '%United States%' AND (ad.catid = search.catid OR ad_cat.catfatherid = search.catid)
        GROUP BY ad.catid

The above method is much better than my initial attempt. It runs and does not loop through several times, but instead it loops through and gets all the counts without needing to do additional queries. This will also return United States children or if searching for a State will just return that states info.

Original Answer Below:

SELECT ad.title,
         p1.catid search_id, p1.catfatherid search_father_id, fc.catname search_fathername, p1.catname search_name, 
         p2.catname child_cat, p2.catid child_catid, count(*) total 
        FROM category p1,
              category p2 RIGHT JOIN ad ON ad.catid = p2.catid,
              category f RIGHT JOIN category fc ON fc.catfatherid = f.catid
WHERE p1.catname LIKE '%United States%' AND 
        p2.catid=(SELECT catid FROM category WHERE catid=if(p1.catfatherid=0, p2.catid, p1.catid))
GROUP BY ad.catid ORDER BY search_name

The total in this query is obviously off. And will require you to do an additional query per result.

PHP CODE:
  while($f=mysql_fetch_assoc($sql1)){
     $tq = mysql_query("select count(*) total from ad where catid='{$f['child_cat']}'");
     $t = mysql_fetch_assoc($tq);
     echo $t['total']; //Gives you total per result;
  }

I can't guarentee this is the best method as it does appear to be heavy as it seems to pull about 8+4+4 rows looking at the total and takes about 0.9ms on this little bit of data. (I added couple extra rows in Arizona category 'ad table')

Hopefully someone else here can take this and optimize this. I can't think of another way at this moment to do this.

1. So you want to include an ad like this?

   adid  catid      title
       4   1         USA Item1
       5   1         USA Item2
   

   use this query:

   SELECT category.catid, 
          category.catfatherid, 
          category.catname, 
          a.adid, 
          a.title 
   FROM   ad a 
          LEFT JOIN category AS category 
            ON a.catid = category.catid 
   WHERE  category.catname LIKE '%%Arizona%%' 
           OR category.catid IN (SELECT catfatherid 
                                 FROM   category 
                                 WHERE  catname LIKE '%%Arizona%%') 
   

2. Or you want to include also the ads of Arkansas Item and California Item that belong under United States category, category given by the LIKE?

   SELECT category.catid, 
          category.catfatherid, 
          category.catname, 
          a.adid, 
          a.title 
   FROM   ad a 
          LEFT JOIN category AS category 
            ON a.catid = category.catid 
   WHERE  category.catfatherid IN (SELECT catfatherid 
                                   FROM   category 
                                   WHERE  catname LIKE '%%Arizona%%') 
   

SELECT category.catid, category.catfatherid, category.catname, A.adid, A.title 
FROM ad A
LEFT JOIN category AS category ON A.catid = category.catid
WHERE 
category.catfatherid = (select catfatherid
FROM category WHERE catname LIKE '%United States%' limit 1)

Might work with a subselect or be worth doing at two queries as would be lighter on the system.