Problems with MySQL OR clause

Posted by Simon on Stack Overflow See other posts from Stack Overflow or by Simon
Published on 2010-03-11T18:38:45Z Indexed on 2010/03/11 18:44 UTC
Read the original article Hit count: 193

Filed under:

I have a problem with AND and OR clause in SQL. Here is my query:

SELECT  
    `act1`.`name`,
    `act1`.`surname`,
    `act2`.`name`,
    `act2`.`surname`,
    `act3`.`name`,
    `act3`.`surname`
FROM 
    `videos`,
    `actors` AS act1,
    `actors` AS act2,
    `actors` AS act3
WHERE 
    `videos`.`ident` = 'somethink' 
  AND 
    (
        `act1`.`id` = `videos`.`id_actor1`
      OR 
        `act2`.`id` = `videos`.`id_actor2`
      OR
        `act3`.`id` = `videos`.`id_actor3`
    )

It returns me all variations of {name, surname}, but I want the name and surname of first exactly, second and thirth, if they exist.

© Stack Overflow or respective owner

Related posts about mysql