CODEWITHSUNDEEP
mysqlsql
Theophile
Theophile

Reputation: 21

Subquery in the WHERE clause exercise

I have a migration table with attributes (name, departure year) and a stork table with attributes(name, year of birth). I want to write a query giving the names of all storks that migrated before the birth of the youngest stork which is 2002 (using the migration table in the main query and the Stork table in a sub-request of the WHERE clause). N.B: Not allowed to use a clause such as WHERE, GROUP BY, ORDER BY, LIMIT, unless it is necessary/mandatory 

Migration table
Name        DEPARTURE YEAR   
Annamarie   2001   
Felix       2002
Annamarie   2003
Felix       2004
Jonas       2001

Stork table
Name       YEAROFBIRTH
Annamarie   1998
Felix       1999
Max         2000
Jonas       2001
Christina   2002

Using the DISTINCT operator gives the desired result with 15 names, removing the DISTINCT operator gives me 25 names with duplicates

SELECT DISTINCT migration.NAME
FROM migration
WHERE migration.DEPARTUREYEAR IN(SELECT stork.YEAROFBIRTH
FROM stork
WHERE stork.YEAROFBIRTH <2002)

I expect to obtain the 15 names without using the DISTINCT operator

Upvotes: 2

Views: 64

Answers (2)

mkRabbani
mkRabbani

Reputation: 16918

Try this-

SELECT NAME -- You can apply DISTINCT if required 
FROM Migration
WHERE Name IN (
    SELECT Name FROM Stork
    WHERE YEAROFBIRTH < 2002
)

Upvotes: 0

Uueerdo
Uueerdo

Reputation: 15961

Just going by the desired result, and your example's use of the literal year; this should be all you need.

SELECT DISTINCT migration.NAME
FROM migration
WHERE migration.DEPARTUREYEAR < 2002
;

However, if you want to not use a literal year, the subquery can be used to determine it like so:

SELECT DISTINCT migration.NAME
FROM migration
WHERE migration.DEPARTUREYEAR < (SELECT MAX(stork.YEAROFBIRTH) FROM stork)
;

Upvotes: 1

Related Questions