Use select result for WHERE clause

Question

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!

Is it possible to do it in one SQL query? Have you tried it? — Iswanto San
– Iswanto San, Commented Oct 30, 2013 at 6:24

i5h4n · Accepted Answer · 2013-10-30 06:29:09Z

3

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

answered Oct 30, 2013 at 6:29

i5h4n

3872 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user2864740 · Accepted Answer · 2013-10-30 06:43:56Z

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:

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.
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.

Collectives™ on Stack Overflow

Use select result for WHERE clause

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related