MYSQL join error. How to resolve?

Question

MYSQL Query:

SELECT * FROM `pet_info` LEFT JOIN `lostpets` ON `pet_info`.`id` = `lostpets`.`petid` LEFT JOIN `pet_images` ON `pet_info`.`id` = `pet_images`.`petid` LEFT JOIN ON `pet_info`.`pet_cat` CASE WHEN `pet_info`.`pet_cat` = 1 THEN `dog_breeds`.`id` WHEN `pet_info`.`pet_cat` = 2 THEN `cat_breeds`.`id` END WHERE `pet_info`.`pet_user_id` = 581

Error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON `pet_info`.`pet_cat` CASE WHEN `pet_info`.`pet_cat` = 1 THEN `do' at line 7

Why error is appearing please help me. Gow can I resolve this error?

'LEFT JOIN ON'. - Some basic formatting would probably help. — Strawberry, Apr 16 '17 at 16:37
I agree with @Strawberry it looks like you're missing an entire table name in there. By the way, please kill those backticks, which makes it even harder to read your single line query. — Tim Biegeleisen, Apr 16 '17 at 16:39
Your problem may lie in the backticks. http://stackoverflow.com/questions/261455/using-backticks-around-field-names — dragonfire, Apr 16 '17 at 16:39
Your final `LEFT JOIN` makes no sense because there is no table mentioned. And there is a `CASE` expression there which might be out of place. — Tim Biegeleisen, Apr 16 '17 at 16:41
See http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Apr 16 '17 at 16:43
Looks like you are trying to use one join for two different tables. This is not possible. Format your query and explain, what you are trying to do. — Paul Spiegel, Apr 16 '17 at 16:43
if value = 1 then joining with dog breed table else joining with cat breed — Shah Rushabh, Apr 16 '17 at 16:45

score 0 · Answer 1 · answered Apr 16 '17 at 16:41

Here's a formatted version of your query, highlighting some errors.

SELECT * 
  FROM pet_info p
  LEFT 
  JOIN lostpets l
    ON p.id = l.petid 
  LEFT 
  JOIN pet_images i
    ON p.id = i.petid 
  LEFT 
  JOIN  !! ERROR 1
    ON p.pet_cat !! ERROR 2
  CASE WHEN p.pet_cat = 1 
       THEN d.id !! ERROR 3
       WHEN p.pet_cat = 2 
       THEN c.id END !! ERROR 4
 WHERE p.pet_user_id = 581

MYSQL join error. How to resolve?

1 Answers1