Re: More dumb SQL questions ...

Hi Roger

I know your example was intended to be simplistic but you don't seem to have a join in your exist clause to the outer table so if any one row in mytable satisfies the inner query then that will satisfy the exist clause for all rows of mytable as per the outer.

If you are unable to join to the subquery then you could change the construct to

   where something in ( select ...etc... having....)

Also this would be much more efficient if mytable is large but the dataset of the subquery is small.

With an exist although it is very efficient it is also important that the exist clause is applied after reducing the result set as much as possible. In your example there would be a full table scan of mytable to check every row against the exist. With the In clause the IN is evaluated first and then applied to mytable.    

As I say the example is simplistic and I expect you know all this already.

But I hope its some help,

regards

Rod Corderey
Lane Associates
Lane_Associates_at_Compuserve.com
http://www.Lane-Associates.com

Roger Loeb wrote:
>
> How do I return columns from a select that has a "group by" clause when
> those columns are not part of the group by?
>
> What I want:
>
> Select * from MyTable where exists (Select something from MyTable group by
> something having count(someotherthing) > 1);
>
> but this returns all rows in MyTable, not just the rows that meet the group
> ... having criterion.
>
> Rog
>
> --
> roger_at__delete_this_to_reply_.martech.com
Received on Mon May 11 1998 - 11:35:32 CEST