CODEWITHSUNDEEP
mysql
Simon
Simon

Reputation: 23141

Problems with MySQL OR clause

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` = 'something' 
  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 third, if they exist.

Upvotes: 2

Views: 139

Answers (3)

Felix Kling
Felix Kling

Reputation: 816404

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.

Upvotes: 0

Peter Lang
Peter Lang

Reputation: 55524

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.

Upvotes: 4

Mark Byers
Mark Byers

Reputation: 838156

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'

Upvotes: 1

Related Questions