Naming a series of subqueries (PHP/MySQL)

Question

I figured out how to display a list of duplicate values in a particular database table...

$stm = $pdo->prepare("SELECT URL, COUNT(*) tot
 FROM people
 GROUP BY URL
 HAVING tot > 1");
$stm->execute(array(
  'Total'=>$Total
));

while ($row = $stm->fetch())
{
 $URL_Dupe = $row['URL'];
 $tot = $row['tot'];
 $Dupe2[] = ''.$URL_Dupe.''.$tot.'';
}

I'd like to do the same thing with a series of tables linked together with a UNION ALL clause...

$stm = $pdo->prepare("SELECT 'GZ' AS GSiteID, NULL as Site, 'Life' AS GSection, GZL.Taxon AS URL
 FROM gz_life GZL WHERE GZL.Taxon = :MyURL
 UNION ALL
 SELECT 'All' AS GSiteID, NULL as Site, 'World' AS GSection, GG.Name AS URL FROM gw_geog GG WHERE GG.Name = :MyURL
 UNION ALL
 SELECT 'PX' AS GSiteID, Site, 'People' AS GSection, Ppl.URL FROM people Ppl WHERE Ppl.URL = :MyURL");
$stm->execute(array(
 'MyURL'=>$MyURL
));

In other words, I want to track down any word that appears more than once in a single table or once (or more) in two or more different tables.

But I think I have to first create a single name for all these tables (e.g. "Todo"), then perform the operation on that name, right? How do I do that? I have a similar table that simply ends with "AS X." But I can't do that on this query without getting errors.

Gordon Linoff · Accepted Answer · 2014-01-25 02:21:24Z

1

You can make the union all query a subquery and then aggregate them like:

select url, count(*)
from ((SELECT 'GZ' AS GSiteID, NULL as Site, 'Life' AS GSection, GZL.Taxon AS URL
       FROM gz_life GZL WHERE GZL.Taxon = :MyURL
      ) UNION ALL
      (SELECT 'All' AS GSiteID, NULL as Site, 'World' AS GSection, GG.Name AS URL
       FROM gw_geog GG WHERE GG.Name = :MyURL
      ) UNION ALL
      (SELECT 'PX' AS GSiteID, Site, 'People' AS GSection, Ppl.URL
       FROM people Ppl WHERE Ppl.URL = :MyURL
      )
     ) t
group by url;

If you really want to meet the condition "I want to track down any word that appears more than once in a single table or once (or more) in two or more different tables". That is equivalent to the count being at least 2, so add a having clause:

select url, count(*)
from ((SELECT 'GZ' AS GSiteID, NULL as Site, 'Life' AS GSection, GZL.Taxon AS URL
       FROM gz_life GZL WHERE GZL.Taxon = :MyURL
      ) UNION ALL
      (SELECT 'All' AS GSiteID, NULL as Site, 'World' AS GSection, GG.Name AS URL
       FROM gw_geog GG WHERE GG.Name = :MyURL
      ) UNION ALL
      (SELECT 'PX' AS GSiteID, Site, 'People' AS GSection, Ppl.URL
       FROM people Ppl WHERE Ppl.URL = :MyURL
      )
     ) t
group by url
having count(*) > 1;

answered Jan 25, 2014 at 2:21

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user2469520 Over a year ago

Thanks, but I must be doing something wrong with my while loop, as I get the error message "Undefined index: URL." (Note: I changed group by url to group by URL.) This is my while loop: while ($row = $stm->fetch()) { $URL = $row['URL']; } echo join( $URL, '<br>' );

Gordon Linoff Over a year ago

That sounds like a php error. I'm not sure why that would be happening.

user2469520 Over a year ago

Just a note that I finally figured out the problem(s). First, I made a stupid mistake in not deleting the WHERE clauses. I also had to turn the value $URL into an array as discussed @ stackoverflow.com/questions/21358773/….

Collectives™ on Stack Overflow

Naming a series of subqueries (PHP/MySQL)

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related