You can use GROUP BY or upgrade your hardware :)
----- Original Message -----
From: <[EMAIL PROTECTED]>
To: mySQL list <[EMAIL PROTECTED]>
Sent: Wednesday, January 31, 2001 9:42 PM
Subject: can I select from one table only where some records exist in
anothertable?
> Sorry, that's not a very good formulation of the question.
>
> I have 2 tables, and I want to retrieve data from records in table
> A only if there are corresponding records in B. I don't care what's in the
> records in table B: all I want to know is which items in A _have_ records
> in B.
>
> If I do:
> select distinct A.this, A.that from A, B
> where A.key=B.key
> order by A.key
> then the join produces zillions of records and the query takes ages.
>
> Similarly if I do
> select distinct A.this, A.that from A
> left join B on A.key=B.key
> where B.key is not null
> it takes ages.
>
> I guess if I could somehow apply 'limit 1' to the join, so that I
> just get any one record from B (I don't care which record: after all I'm
> not using data from B) then it would go like greased lightning, but I
> can't see how to express that in mySQL SQL. Is this one of those things
> that can't be done without sub-selects? Something like:
> select A.this A.that from A
> where (select count(*) from B where B.key=A.key)>0
> (I'm not sure if that's valid SQL, but I hope it conveys my meaning.)
>
>
> regards,
>
> --
> John Stumbles
[EMAIL PROTECTED]
> I.T. Services Centre, University of Reading
http://www.rdg.ac.uk/~visstmbl
>
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
+
> never generalise
>
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
