Displaying Duplicates from UNION ALL Table

Databases
1

I figured out how to display a list of duplicate values from an individual database table. However, I haven’t yet figured out how to display duplicates in a series of tables.

Examples of the kinds of duplicates I might expect include URL = George_Washington (the name of a U.S. president displayed at MySite/People/George_Washington and a ship @ MySite2/Topics/George_Washington) and URL = Gary_Locke (a person discussed in two articles at MySite/People/Gary_Locke and MySite2/People_Gary_Locke).

In summary, I simply want to display a list of every value in the field URL (which corresponds to the fields Taxon and Name). It would be really cool if I could display a list of URL’s with their respective sites and sections, like this:

George_Washington | MySite | People
George_Washington | MySite2 | Topics

But if all I can do is display a simple list of URL’s, that would be fine. I’m going to be doing a lot of work with duplicates, and I need to learn some tricks for manipulating them. Thanks.


  $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 'GS' AS GSiteID, NULL as site, 'World' AS GSection, GS.URL FROM gs GS WHERE GS.URL = :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
 ));
2

easiest approach here is to declare a view for your UNION query (but without the WHERE conditions that isolate a single url)

then just go ahead and pull the duplicates using the same sql that you’ve used before to pull duplicates from a table

3

Thank, r937; that sounds like something cool to learn about.

P.S. Is CREATE VIEW something that can only be done in phpMyAdmin > SQL, or can it be used to replace a more traditional query on a web page? It looks like an SQL thing to me, but some of the tutorials I’ve checked out make it sound like people actually use it on their web pages.