Jump to content

Subquery, temp table, or ???


binarynomad

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?

Link to comment
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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.