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?
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
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'
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0