Subquery, temp table, or ???

[binarynomad]

############################################################

# PROBLEM:

############################################################

 

How can I select rows in PHP that require two select

statements on the same table to work togeather?

 

See the example:

 

############################################################

# EXAMPLE DATABASE TABLE NAMED \"X\":

############################################################

 

<DATE> <NAMES>

---------- -------

2003-01-08 Brian

2003-01-08 John

2003-01-07 Sally

2003-01-04 Michael

2003-01-03 Brian

2003-01-02 Alex

2003-01-02 Sally

2003-01-01 John

 

############################################################

# QUERY:

############################################################

 

Plain text: \"Give me all the names of the last three unique date entries.\"

 

SELECT names FROM x WHERE date IN (SELECT date FROM x GROUP BY date DESC LIMIT 3);

 

############################################################

# LAST 3 UNIQUE DATES:

############################################################

 

2003-01-08

2003-01-07

2003-01-04

 

############################################################

# RESULTS:

############################################################

 

Brian

John

Sally

Michael

 

############################################################

# ISSUE:

############################################################

 

The select query above doesn\'t work, so I am stuck. MySQL doesn\'t seem to support multiple queries in one line. It looks like I will have to have PHP run 2 queries seperately:

 

1. to pull the last 3 unique dates.

2. to pull all the names that match the last three dates.

 

The problem is that I am working with a fixed result set in one query, and a open amount result set in the second query.

 

Any suggestions?

[pallevillesen]

USE test; 

CREATE TEMPORARY TABLE dates AS (SELECT date FROM x GROUP BY date DESC LIMIT 3); 

SELECT names FROM x LEFT JOIN dates d ON x.date=dates.date WHERE x.date IS NOT NULL; 

DROP TABLE dates;

 

All your wrong names are NULL in the x.date field after the join. Probably this could be done smarter - but it\'s sunday (i.e. no work or thinking).

 

(Smarter solution is to make an inner join, but I\'m too lazy to look up the correct syntaxt for that.) Aargh... ok

 

USE test; 

CREATE TEMPORARY TABLE dates AS (SELECT date FROM x GROUP BY date DESC LIMIT 3); 

SELECT names FROM x INNER JOIN dates d ON x.date=dates.date;

DROP TABLE dates;

 

The inner join only returns rows that have a dates.date value <> NULL after the join.

 

P., denmark