Problems with MySQL OR clause

Question

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.

Answer 1

Sounds like you need LEFT JOINS.

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

The syntax you use is deprecated anyway, use JOIN and LEFT JOIN instead of listing table names in the FROM clause.

---

Your question sounds as if you could improve your model though. What happens when a video has more than three actors?

Using an intersection table video_actors

• video_id
• actor_id

would allow to do that.

Answer 2

Use joins:

SELECT  
    `act1`.`name`,
    `act1`.`surname`,
    `act2`.`name`,
    `act2`.`surname`,
    `act3`.`name`,
    `act3`.`surname`
FROM `videos`
JOIN `actors` AS act1 ON `act1`.`id` = `videos`.`id_actor1`
LEFT JOIN `actors` AS act2 ON `act2`.`id` = `videos`.`id_actor2`
LEFT JOIN `actors` AS act3 ON `act3`.`id` = `videos`.`id_actor3`
WHERE `videos`.`ident` = 'somethink' 

Answer 3

Because afaik MySQL does Short-circuit evaluation when evaluating the where clause and thus creating the Cartesian product, in your case between the other actor tables. As others already mentioned, LEFT JOIN is much more applicable.