> >> 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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
