CODEWITHSUNDEEP
mysqlsql
Michael Zelensky
Michael Zelensky

Reputation: 2132

Use select result for WHERE clause

There is a table {id | date}. I want to select all rows where date > date from the given row.

Normally I would do it in two queries: 

SELECT `date` FROM `mytable` WHERE `id`={id}; //get {date} from known {id}

and

SELECT * FROM `mytable` WHERE `date`> {date} //get the desired result

Is it possible to do it in one SQL query?

Thanks!

Upvotes: 1

Views: 5951

Answers (2)

user2864740
user2864740

Reputation: 61985

This is a good candidate for a self-join.

SELECT o.*
FROM mytable t         -- For every row from This table,
CROSS JOIN mytable o   -- and for every row in the Other (see note #2) table
WHERE t.id <> o.id     -- such that it is a different row
  AND o.date > t.date  -- with a date later
  AND t.id = {id}      -- than a specific row.

Notes:

  1. Because > is used in the value comparison, the t.id <> o.id clause can be omitted. In any case, the query planner should figure this stuff out just fine.
  2. The CROSS JOIN starts off a Cartesian product but the WHERE filters bring it back down quickly. In particular the t.id = {id} clause (assuming that id has a unique constraint) brings down the multiplicity to at most one output row for each input row in o.

Upvotes: 0

i5h4n
i5h4n

Reputation: 387

select * from mytable where date > (select date from mytable where id = {id})

Upvotes: 3

Related Questions