#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    5
    Rep Power
    0

    Is a subquery the solution?


    Hi all,

    I am very new to mySQL and hope that somebody can help me.

    In 1 of my tables (tblPersons) there are (amongst others) the following fields:
    prsID, prsFirstName, prsPrefix, prsLastName, prsCountry, prsBirthDate and prsNatRef.

    I need to find out which combinations of prsFirstName, prsPrefix, prsLastName, prsCountry occur more than once.

    The (I think) 1st step is fairly easy:
    Code:
    SELECT prsFirstName, prsPrefix, prsLastName, prsCountry, COUNT(prsLastName) 
    FROM tblPersons 
    WHERE COUNT(prsLastName) > 1 
    GROUP BY prsFirstName, prsPrefix, prsLastName, prsCountry
    This (part of the) query gives me all names that occur more than once.
    The next step is to get the remaining fields connected to these records.

    Is it possible to do this with 1 query?
    Might using a subquery be the answer?And how should I do that?

    Thanks in advance
    Hein
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,262
    Rep Power
    4279
    yes, it's possible to do this with one query, and yes, using the query you posted (with corrections for syntax errors) as a subquery is the answer
    Code:
    SELECT tblPersons.*
      FROM ( SELECT prsFirstName
                  , prsPrefix
                  , prsLastName
                  , prsCountry
               FROM tblPersons 
             GROUP 
                 BY prsFirstName
                  , prsPrefix
                  , prsLastName
                  , prsCountry 
             HAVING COUNT(*) > 1 ) AS dupes 
    INNER
      JOIN tblPersons.prsFirstName = dupes.prsFirstName  
        ON tblPersons.prsPrefix    = dupes.prsPrefix    
       AND tblPersons.prsLastName  = dupes.prsLastName  
       AND tblPersons.prsCountry   = dupes.prsCountry
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    5
    Rep Power
    0
    Brilliant! Thank you Rudy!

    I am glad to see that my thought about using a subquery was correct (I have so far no experience using this kind of query but in my search for the answer I came across it and it seemed right).

    If I copy and paste your code into my database it gives an error on line 14: JOIN tblPersons.prsFirstName = dupes.prsFirstName

    apparently the "." from dupes.prsFirstName causes a problem. It gets red underlined. (mySQL only gives the location of the error, no extra information is given)
    Is it possible to correct this?

    If I may I would like to ask you another question. Just as a learning experience:
    what is the theory or thought behind putting the subquery in the From statement. It looks as if you are creating a temporary table which gets joined to tblPersons in the last bit of your code?

    Anyway: hopefully the dupes.prsFirstName can be corrected.

    maybe as an extra bit of information: I am using mySQL Workbench

    Thanks Hein
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,262
    Rep Power
    4279
    AAAACK!! sorry for the syntax error

    less haste, more speed
    Code:
    SELECT tblPersons.*
      FROM ( SELECT prsFirstName
                  , prsPrefix
                  , prsLastName
                  , prsCountry
               FROM tblPersons 
             GROUP 
                 BY prsFirstName
                  , prsPrefix
                  , prsLastName
                  , prsCountry 
             HAVING COUNT(*) > 1 ) AS dupes 
    INNER
      JOIN tblPersons
        ON tblPersons.prsFirstName = dupes.prsFirstName  
       AND tblPersons.prsPrefix    = dupes.prsPrefix    
       AND tblPersons.prsLastName  = dupes.prsLastName  
       AND tblPersons.prsCountry   = dupes.prsCountry
    Originally Posted by Sport
    what is the theory or thought behind putting the subquery in the From statement. It looks as if you are creating a temporary table which gets joined to tblPersons in the last bit of your code?
    exactly right

    a subquery in the FROM clause is called a derived table or inline view

    it works just like a temporary table, except it isn't really a temporary table

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    5
    Rep Power
    0
    Rudy,

    It works perfectly! Thank you, also for the learning experience.
    This will improve my queries that I hope to write in the future big-time

    Thanks again for your help!
    Hein

IMN logo majestic logo threadwatch logo seochat tools logo