CODEWITHSUNDEEP
mysql
max
max

Reputation: 3716

Join with second table based on first table column

hi i want to join my registry_ug table with a second table based on registry_ug.type.... i want to avoid the unnecessary join .

here is my code

   SELECT  ug.id , tbl.id FROM registry_ug ug
      JOIN    CASE ug.type 
             WHEN 1   THEN  users 
             WHEN 2   THEN  group_software 
       END
       AS  tbl
       ON 
       ug.id = tbl.id 

     WHERE ug.id = $uid

it seems ok to me but i get this 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 'CASE ug.type WHEN 1 THEN users WHEN 2 THEN g' at line 2

SELECT ug.id , tbl.id FROM registry_ug ug JOIN CASE ug.type WHEN 1 THEN users WHEN 2 THEN group_software END AS tbl ON ug.id = tbl.id WHERE ug.id = 55

*and i dont want to join it to all of possible second tables *

Upvotes: 0

Views: 88

Answers (2)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

You can't do this this way. You can't use CASE expression this way. Try this instead:

SELECT  
  ug.id, 
  COALESCE(u.id, g.id)
FROM registry_ug AS ug
LEFT JOIN users          AS u ON ug.id = u.id AND ug.type = 1
LEFT JOIN group_software AS g ON ug.id = g.id AND ug.type = 2
WHERE ug.id = $uid

Upvotes: 1

John Woo
John Woo

Reputation: 263723

give this a try, 

SELECT  ug.id , 
        CASE WHEN ug.type = 1 
            THEN b.id 
            ELSE c.id
        END newID
FROM    registry_ug ug
        LEFT JOIN   users b
            ON ug.id = b.id 
        LEFT JOIN   group_software c
            ON ug.id = c.id 
WHERE   ug.id = $uid

can't test, no sample data added

Upvotes: 0

Related Questions