Geek Answers Handbook

How to write the correct If ... Else Statement with JOIN in MySQL?

I am new to MySQL. I just know absolutely basic statements, but now I have to go into a more complex, but worthy thing.

I actually have 3 tables in MySQL, here is a view:

users:

   user_id | name    | country 
   ---------------------------
         1 | Joseph  | US     
         2 | Kennedy | US      
         3 | Dale    | UK

admins:

  admin_id | name    | country
  ----------------------------
         1 | David   | UK     
         2 | Ryan    | US     
         3 | Paul    | UK

Notes:

id | n_id | note                    | comment   | country | type  | manager
----------------------------------------------------------------
 1 | 3    | This is the 1st note    | First     | US      | admin | 2
 2 | 2    | This is the 2nd note    | Second    | US      | user  | 1
 3 | 2    | This is the 3rd note    | Third     | UK      | user  | 2

Now I would like to execute something like this SQL (I will not enter real commands here because I am not very familiar with all SQL expressions):

  IF notes.type = admin 
    THEN 
        SELECT 
            notes.note, 
            notes.comment, 
            notes.country, 
            admins.name, 
            admins.country 
        FROM notes, admins 
        WHERE notes.n_id = admin.admin_id
    ELSEIF notes.type = 'user'
        SELECT
            notes.note, 
            notes.comment, 
            notes.country, 
            users.name, 
            users.country 
        FROM notes, users 
        WHERE notes.n_id = users.user_id

I hope you understand what I would like to achieve here. I could do this easily with a lot of SQL statements, but I would like to try a few queries that don't use as many resources.

Change 1:

, , . , , SQL Admin ( ), , Users.

:

            result:
            ------
                id | note                    | comment  | country | name
                --------------------------------------------------------
                 1 | This is the 1st note    | First    | US      | Paul
                 2 | This is the 2nd note    | Second   | US      | Kennedy 
                 3 | This is the 3rd note    | Third    | UK      | Kennedy

2:

, . Notes " " , , : 2.

+4
2

, :

SELECT n.note, n.comment, n.country, 
       coalesce(a.name, u.name) as name, coalesce(a.country, u.country) as country
FROM notes n left join
     admins a
     on n.n_id = a.admin_id and n.type = 'admin' left join
     users u
     on n.n_id = u.user_id and n.type = 'user';

left join . select.

, :

where n.manager = 2;
+3

, . - :

SELECT 
    notes.note, 
    notes.comment, 
    notes.country, 
    admins.name, 
    admins.country 
FROM
    notes join admins on notes.n_id = admin.admin_id
WHERE
    notes.manager = 2

UNION ALL

SELECT 
    notes.note, 
    notes.comment, 
    notes.country, 
    users.name, 
    users.country 
FROM
    notes join users on notes.n_id = users.user_id
WHERE
    notes.manager = 2
+3

More articles:


All Articles