Jump to content

Archived

This topic is now archived and is closed to further replies.

binarynomad

Subquery, temp table, or ???

Recommended Posts

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

# 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?

Share this post


Link to post
Share on other sites


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

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.