Get SQL results from multiple table based on value in one of the tables-CodePudding

I have a main table (advices) and two reference tables (expert, friend)

advices
----------------------------------------
|id      | advisor_id   | advisor_type |
----------------------------------------
| 1      | 6            | expert       |
| 2      | 6            | friend       |
| 3      | 7            | expert       |
| 4      | 8            | expert       |
----------------------------------------


expert
----------------------------------
|id      | lastname | firstname  |
----------------------------------
| 6      | Polo     |  Marco     |
| 7      | Wayne    |  John      |
| 8      | Smith    |  Brad      |
----------------------------------

friend
----------------------------------
|id      | lastname | firstname  |
----------------------------------
| 6      | Doe      |  John      |
| 7      | Brown    |  Jerry     |
| 8      | Goofy    |  Doofy     |
----------------------------------

I would like to get all of the advices (some are from an expert, some are from a friend) and have their respective lastname and firstname be part of the result set.

Each advice row has reference tables (expert, friend tables) tied to it via the id and type.

So I would like to have a result based on id but depending on type inso far as which table to query

The result would look like this Combining lastname and firstname from reference tables depending on whether it is an expert or a friend.

advices (array)
----------------------------------------------------------------
|id      | advisor_id   | advisor_type   | lastname | firstname |
-----------------------------------------------------------------
| 1      | 6            | expert         | Polo     | Marco     |
| 2      | 6            | friend         | Doe      | John      |
| 3      | 7            | expert         | Wayne    | John      |
| 4      | 8            | expert         | Smith    | Brown     |
-----------------------------------------------------------------

In non programming simple words term I would like to create a query such as this.

   SELECT 
   advices.id, advices.advisor_id, advices.type
 
   IF advices.type==expert THEN expert.lastname, expert.firstname
   ELSE IF advices.type==friend THEN friend.lastname, friend.firstname

   FROM advices, expert, friend

Obviously I know that the SELECT statement does not allow for this type of on the fly logic. But can this be done in another way?

CodePudding user response：

This should work:

SELECT a.*, e.firstname, e.lastname 
FROM advices AS a 
INNER JOIN expert AS e ON a.advisor_id = e.id AND a.advisor_type = 'expert'
UNION
SELECT a.*, f.firstname, f.lastname
FROM advices AS a 
INNER JOIN friend AS f ON a.advisor_id = f.id AND a.advisor_type = 'friend'