correlated subquery

[Guest_imported]

Good day,

Can someone help explain WHAT this correlated subquery is doing; it has stumped me for a couple of days and now I need some help. It looks not too hard, but it is REALLY taxing me. It references a table called ward, and sets up two aliases w1 and w2; number of beds is self explanetary...the query is this:

SELECT *
FROM ward w1
WHERE (SELECT COUNT (*)
FROM ward w2
WHERE w1.number_of_beds < w2.number_of_beds ) < 4

Obviously I can run the query, and I do not need to know specifics of the returned answer ( hence I have supplied JUST the query ), but I do not know the logic of what this is asking;

Any help greatly appreciated,

Regards - Mushki

 

[crufty]

The correlated subquery calculates the number of rows where the value represented by w1.number_of_beds is less than all the values in w2.number_of_beds. The query then goes on to see if this value is less than 4, if it is, select everything from the row containing w1.number_of_beds.

Example (with dummy values for illustration):

TABLE: ward

number_of_beds location
1 Mars
2 Pluto
3 Jupiter
4 Earth
5 Saturn

w1.number_of_beds = 1 -> correlated subquery = 4
w1.number_of_beds = 2 -> correlated subquery = 3
w1.number_of_beds = 3 -> correlated subquery = 2
w1.number_of_beds = 4 -> correlated subquery = 1
w1.number_of_beds = 5 -> correlated subquery = 0

so the total query will return all rows EXCEPT for the row where number_of_beds = 1.

 

[pcorreia]

If I understand this query (and admittedly, it's a squirrely one), this query is selecting information on the 4 wards with the highest number of beds. If the RDBMS implemented the TOP keyword, you might be able to write it this way:

SELECT TOP 4 *
FROM ward
ORDER BY number_of_beds

Anybody want to weigh in and say that I understand this correctly?

 

[PruSQLer]

You're right in that it picks the 4 wards with the highest number of beds and that the query appears to be pretty squirrely. As far as &quot;TOP&quot; goes, I tried that in Access and it gave me the first 4 rows in the table, not the top values. DB2 didn't recognize &quot;TOP&quot;. Those are the only 2 RDBMS's I have to experiment with. The correlated subquery definitely works though.

 

[Guest_imported]

Thankyou for your replies, I can sort of see what is going on now...oh, and what does 'squirrely' mean ??

 

[pcorreia]

Heh... evasive, dodgy?