Jump to content


Photo

Subquery, temp table, or ???


  • Please log in to reply
1 reply to this topic

#1 binarynomad

binarynomad
  • New Members
  • Pip
  • Newbie
  • 1 posts

Posted 10 January 2003 - 09:30 PM

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

#2 pallevillesen

pallevillesen
  • Members
  • PipPipPip
  • Advanced Member
  • 135 posts
  • LocationDenmark

Posted 12 January 2003 - 06:01 PM


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
Palle Villesen, www.birc.dk [br]Bioinformatics Research Center




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users