How to write a proper If...Else Statement with JOIN in MySQL?

mysql, sql

Solution

Here is a method that you can do in one query:

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';

This uses `left join` to bring the records together from both tables. It then chooses the matching record for the `select`.

To select a particular manager, remove the semicolon and add:

where n.manager = 2;

Problem

I'm quite a beginner in MySQL I just know the totally basic statements, however now I'ts time for me to get into some more difficult, but worth stuff. I actually have 3 tables in MySQL, here is the representation: 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'm going to type not real commands here, because I'm not really familiar with all of the 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 would I like to achieve here. I could do this easily with more SQL statements, but I would like to try some query which doesn't use that much resources. Edit 1: I would like to Get all of the Notes and get which usergroup has submitted it than apply the user's name to it. I mean, if the admin submitted the note, than SQL should choose the ID from the Admin table (as per the type value) but if a User submitted the note, it should get the name from the Users table. The result should look something similar to this: ``` 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 ``` Edit 2: I have actually forgot to mention, that all of these should be listed to a manager. So a 'manager ID' should be added to the Notes and list all of the notes where the manager is for example: 2.

Original source