Hmm, I think the only time you really can't avoid a nested query is when you want to do a grouping function on the result of a grouping function. Like count the number of customers who have more than a certain number of orders. Something like:
SELECT COUNT(cust.id) FROM (SELECT cust.id, COUNT(order.id) AS order_count FROM cust, order WHERE order.cust_id=cust.id GROUP BY cust.id) WHERE order_count>10 ; Oracle has a special syntax that can handle derived values from derived values called roll-ups that I believe can handle things like this, but I don't think that's a standard SQL syntax. jaime -----Original Message----- From: Jay Sprenkle [mailto:[EMAIL PROTECTED] Sent: Sunday, April 08, 2007 2:44 PM To: [email protected] Subject: Re: [sqlite] Nested SELECT : Alternative syntax please ? On 4/8/07, Jaime Castells <[EMAIL PROTECTED]> wrote: > > Actually, Jay, I've bumped into cases where you couldn't avoid a nested > query. Me too, but usually only in Access. > I think the problem in Yannick's query was the attempt to alias a table > name. In the above, I alias the field name in the sub query and reference > it without a table name. I agree, but it doesn't look like there's any need to have a nested query at all. ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
