SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict
    Join Date
    Jan 2012
    Posts
    266
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Displaying Duplicates from UNION ALL Table

    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.

    Code:
      $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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Jan 2012
    Posts
    266
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •