MySQL subquery - Dev Shed

Dev Shed Forums Sponsor:

February 6th, 2012, 12:54 AM

shart000

Registered User

Join Date: Nov 2004

Posts: 4

Time spent in forums: 3 h 18 m 42 sec
Reputation Power: 0

MySQL subquery

Hello All,
Thank you in advance for the assist.

I have two tables from two developers, and need to find:
1. The country codes in unique country codes in wp_event_details after 2011-02-08 that are not also before 2011-02-08.
2. From those results I need to know which country codes are also not in wp_r

I have tried to do this myself and I don't think I have enough hair left to finish it. This is my latest attempt at point 1:

SELECT DISTINCT `event_country` FROM `wp_event_details` WHERE `event_start` < '2011-02-08'
AND WHERE NOT IN (SELECT DISTINCT `event_country` FROM `wp_event_details` WHERE `event_start` > '2011-02-08')

for point 2, I have: select rCountry from wp_r

Thank you for the assist, I appreciate any life line.
Simon.

February 6th, 2012, 01:11 AM

shart000

Registered User

Join Date: Nov 2004

Posts: 4

Time spent in forums: 3 h 18 m 42 sec
Reputation Power: 0

Part One

I think I have part one down to:

Code:

SELECT DISTINCT event_country FROM wp_event_details WHERE event_country NOT IN
     (SELECT `event_country`  FROM `wp_event_details` WHERE `event_start` < '2011-02-08')

Now I only need to know which of those results are not in:

Code:

SELECT DISTINCT rCountry FROM wp_r

February 6th, 2012, 06:23 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 25,046

Time spent in forums: 3 Months 2 Days 22 h 44 sec
Reputation Power: 3829

Code:

SELECT DISTINCT 
       event_country 
  FROM wp_event_details 
 WHERE event_start > '2011-02-08'
   AND event_country NOT IN
       ( SELECT DISTINCT 
                event_country  
           FROM wp_event_details
          WHERE event_start < '2011-02-08' )
   AND event_country NOT IN
       ( SELECT DISTINCT 
                rCountry 
           FROM wp_r )

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

Viewing: Dev Shed Forums > Databases > MySQL Help > MySQL subquery

« Previous Thread | Next Thread »

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded 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