sql - How to select column from different tables based on condition in single query?

Question

Is it possible to get the date column from the different table based on conditions in a single query?

1. In SPE_common_data is the Main table, there is spe_content_type column has JOURNAL PAPER or CONFERENCE PAPER are values.

2. If the column value is JOURNAL PAPER – use SPE_journal_data table and use spe_journal_pub_online_date as active date to publish paper

3. IF the column value is CONFERENCE PAPER – use SPE_conference_data table to find spe_meeting_code for that paper

4.And based on that spe_meeting_code go to Petro_meetings table and find early_start_date for that meeting code and use that date to publish that paper.

Is this possible for everything in a single query?

question from:https://stackoverflow.com/questions/66057935/how-to-select-column-from-different-tables-based-on-condition-in-single-query

Answer 1

Try to union two queries by the set_content_type value, for example like this:

SELECT 
  cmn.spe_manuscript_num,
  cmn.spe_content_type,
  jrn.spe_journal_pub_online_date AS pub_online_date 
FROM spe_common_data cmn
JOIN spe_journal_data jrn ON cmn.spe_manuscript_num = jrn.spe_manuscript_num
WHERE cmn.spe_content_type = 'JOURNAL PAPER'
UNION ALL
SELECT 
  cmn.spe_manuscript_num,
  cmn.spe_content_type,
  pm.early_start_date AS pub_online_date 
FROM spe_common_data cmn
JOIN spe_conference_data cnf ON cmn.spe_manuscript_num = cnf.spe_manuscript_num
JOIN petro_meetings pm ON cnf.spe_meeting_code = pm.spe_meeting_code 
WHERE cmn.spe_content_type = 'CONFERENCE PAPER'