Double select from a single table in complex SQL query.

At Tuesday, June 02, 2009

ADSENSE HERE!

When I start learning SQL I have to write a lot of nested queries when working on complex select statements from a single table. Now, I feel more comfortable with it and show how to avoid nesting. There are cases when you have to compare one row of the table with all others to get the result:

• Select next element to current by the value of some field


• Select records that are unique by some complex expression


• Select all records that have the same parameter as given record.

You can get rid of nesting in all above cases. Let's review the example.

---

Let's say we have the list of elements in `elements` table and we have the id of some element in this table. Now we have to get the next element from the list ordered by `sort_field`.
That could be easily done with the nested select statement:

select * from elements e 
where e.sort_field >= 
            (select * from elements where element_id = #value#) and
         e.element_id != #value#
order by e.sort_field limit 1;

Looks not very cool.
Let's do the select statement from `element` table twice to avoid nesting:

-- The e1 is the result row and e2 is a helper row 
--that was picked up using nested select previously.
select e1.* from elements e1, elements e2
--conditional statement will look like
where e1.sort_field >= e2.sort_field and 
     e1.element_id != e2.element_id and 
     e2.element_id = #value#
-- and the end of the query remains almost the same
order by e1.sort_field limit 1

Do not scare of double select from single table. That is the same technique as if they were different tables.

ADSENSE HERE!