>> >> SELECT R1.* from R1 left join R2 on (R1.id=R2.pid) where R2.id is NULL; >> What i need is to have a result that list only the R1 rows which does not >> have any references of type "D". > >Aha, you have more than one record in R2 which is linked to R1. > >Hmmm.. you can select the pid's from R2 which do have a type="D" into a >temporary table. Then you can select the R1 records which do not have any >references in the temporary table. > >You'll end up with the R1 records that do not have any references in R2 or >do not have a reference with type="D" > >CREATE TEMPORARY TABLE `<unique_name>` SELECT pid FROM R2 WHERE R2.type = >"D"; >SELECT * FROM R1 LEFT JOIN `<unique_name>` AS t1 ON (R1.id=t1.pid) WHERE >t1.pid IS NULL; >DROP TABLE `<unique_name>` >Regards, Jigal.
Yes, you're right. Maybe i was not clear enought in my explanation. I wanted to know a method to do this without creating a temporary table, in a single statement. However, this seems not be possible with this version. (It require to be able to do multiple select in a single statement : Select .. From ... NOT IN ( SELECT ... from .. where...) ... ? So i think i'll do as you suggest. Thank you for your help -- Vincent -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
