MySQL Help
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me

The Shed is going Social! Join us on FaceBook and Twitter and chime in on the conversation.

Go Back   Dev Shed Forums > Databases > MySQL Help
Reload this Page

Is a subquery the solution?


Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old January 21st, 2013, 12:59 PM
Sport Sport is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jan 2013
Posts: 5 Sport User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 3 h 19 m
Reputation 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
Reply With Quote
  #2  
Old January 21st, 2013, 02:36 PM
r937's Avatar
r937 r937 is offline
SQL Consultant
Click here for more information.
  
Join Date: Feb 2003
Location: Toronto Canada
Posts: 26,371 r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level) 
Time spent in forums: 3 Months 1 Week 2 Days 6 h 29 m 14 sec
Reputation Power: 4140
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
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #3  
Old January 21st, 2013, 02:51 PM
Sport Sport is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jan 2013
Posts: 5 Sport User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 3 h 19 m
Reputation 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
Reply With Quote
  #4  
Old January 21st, 2013, 02:55 PM
r937's Avatar
r937 r937 is offline
SQL Consultant
Click here for more information.
  
Join Date: Feb 2003
Location: Toronto Canada
Posts: 26,371 r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level) 
Time spent in forums: 3 Months 1 Week 2 Days 6 h 29 m 14 sec
Reputation Power: 4140
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

Quote:
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
Reply With Quote
  #5  
Old January 21st, 2013, 03:09 PM
Sport Sport is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jan 2013
Posts: 5 Sport User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 3 h 19 m
Reputation 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
Reply With Quote
Reply

Viewing: Dev Shed Forums > Databases > MySQL Help > Is a subquery the solution?

« Previous Thread | Next Thread »

Developer Shed Advertisers and Affiliates



Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.

© 2003-2013 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap