How to add logic in a joining tables SQL query?-CodePudding

I have to create one table based on several tables.

The result table contains several columns : some columns are taken directly from the source tables but other columns require more logic.

In the query below (short example of my problem) you have

SELECT
    cntr.cntr_id, 
    cove.eff_date, cove.end_date,
    amou.amount AS paid_start_date, amou.amount AS paid_curr_date
FROM 
    msa_contract cntr, msa_coverage cove, msa_amount amou
WHERE 
    cntr.cntr_id = '1234'
    AND cove.cntr_id = cntr.cntri_id
    AND amou.cvrg_id = cove.cvrg_id

my problem comes in the two last columns (paid_start_date and paid_curr_date ). These two columns are sourced by the same column amou.amount.

The column paid_start_date should take amou.amount only when amou.start_date = cntr.start_date

The column paid_curr_date should take amou.amount only when cntr.cancelled = 'No'

I don't really see how to integrate these two rules in my query. Can you help me with this?

CodePudding user response：

The column paid_start_date should take amou.amount only when amou.start_date = cntr.start_date

CASE WHEN amou.start_date = cntr.start_date THEN amou.amount END as paid_start_date

The column paid_curr_date should take amou.amount only when cntr.cancelled = 'No'

CASE WHEN cntr.cancelled = 'No' THEN amou.amount END as paid_curr_date

You didn't say whether some other value should be present if the predicate is false, so I didn't put an ELSE. If CASE doesn't find any predicate that is true, and there is no ELSE, the result is NULL

As a footnote, I don't know what tutorial you followed to get advice to join your data like that, but it must be quite an old one because we've been encouraging this form for about 30 years now:

FROM 
    msa_contract cntr
    INNER JOIN msa_coverage cove ON cove.cntr_id = cntr.cntri_id
    INNER JOIN msa_amount amou ON amou.cvrg_id = cove.cvrg_id
WHERE 
    cntr.cntr_id = '1234'

It's virtually the same as what you write, except where you put , we put INNER JOIN and whatever x.y = a.b in the WHERE part relates the data together, we put that after the word ON that follows the right hand table name/alias