CODEWITHSUNDEEP
mysqlsql
Radical_Activity
Radical_Activity

Reputation: 2738

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

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.

Upvotes: 4

Views: 391

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

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;

Upvotes: 3

timo.rieber
timo.rieber

Reputation: 3867

If you expect admins and users in one result you have got several options. The simplest way is to make a union select like this:

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

Upvotes: 3

Related Questions