Joining conditionally based on values in 2 different columns in current table

Question

SELECT  
    l.*,
    qqs.*,
    qq.*,
    --  cqqs.id As Cosigner_Quote_Set_ID,
    --  borrower_cosigner_quote_set_uuid as Cosigner_Quote_Set_UUID,
    --  cqq.id As Cosigner_Quote_ID,
    --  cqq.uuid As Cosigner_Quote_UUID,
    --  cqq.accept_federal_loan As Cosigner_Quote_Loan_Type,
    --  cqq.program_id As Cosigner_Quote_Program_ID,
    --  cqq.lender_name As Cosigner_Quote_Lender_Name,
    --  cqq.term_in_months As Cosigner_Loan_Term,
    --  cqq.apr As Cosigner_Loan_APR,
    --  cqq.monthly_payment As Cosigner_Loan_Pay,
    IF(tentative_quote_uuid IS NOT NULL,1,0) As Quote_Accepted,
    IF(selected_quote_uuid IS NOT NULL,1,0) As Lender_Accepted,
    loan_key As Loan_Key
FROM leads_production.leads l
LEFT JOIN greenhouse_production.citizenship_statuses csb
    ON csb.id = l.citizenship_status_ref
LEFT JOIN greenhouse_production.citizenship_statuses csc
    ON csc.id = l.cosigner_citizenship_status_ref
LEFT JOIN core_production.quotes_quote_sets qqs
    ON qqs.uuid = l.quote_set_uuid
LEFT JOIN core_production.quotes_quotes qq
    ON qq.quote_set_id = qqs.id;
-- LEFT JOIN core_production.quotes_quote_sets cqqs
--    ON cqqs.uuid = l.borrower_cosigner_quote_set_uuid
-- LEFT JOIN core_production.quotes_quotes cqq
--    ON cqq.quote_set_id = qqs.id;

Please look at the commented lines in the above query. I want to write a query wherein the join is either on (qqs and qq tables) OR (cqqs and cqq tables) based on the value in borrower_cosigner_quote_set_uuid.

Something like this:

If borrower_cosigner_quote_set_uuid IS NOT NULL THEN
-- LEFT JOIN core_production.quotes_quote_sets cqqs
--  ON cqqs.uuid = l.borrower_cosigner_quote_set_uuid
-- LEFT JOIN core_production.quotes_quotes cqq
--  ON cqq.quote_set_id = qqs.id;
ELSE
    LEFT JOIN core_production.quotes_quote_sets qqs
        ON qqs.uuid = l.quote_set_uuid
    LEFT JOIN core_production.quotes_quotes qq
        ON qq.quote_set_id = qqs.id;`

Thanks in advance for your help.

Answer 1

Looke like you want a conditional join between TableA, TableB and TableC

If TableB and TableC have similar fields you can join with both tables and solve what field use by checking for the null value

SqlFiddle Demo

Setup Test db

CREATE TABLE TableA
    (`Id` varchar(4), `keyA` int, `valor` int);

INSERT INTO TableA
    (`Id`, `keyA`, `valor`)
VALUES
    ('1', 10, 90),('2', 20, 91),('3', 30, 92),(NULL, 40, 93);

CREATE TABLE TableB
    (`Id` int, `valor` int);

INSERT INTO TableB
    (`Id`, `valor`)
VALUES
    (1, 200),(2, 201),(3, 202);

CREATE TABLE TableC
    (`Id` int, `valor` int);

INSERT INTO TableC
    (`Id`, `valor`)
VALUES
    (10, 500),(20, 501),(30, 502),(40, 503);

QUERY

SELECT A.*,
       IF(A.id is NULL, C.id, B.id) as joinkey,
       IF(A.id is NULL, C.valor, B.valor) as valor
FROM TableA A
LEFT JOIN TableB B
       ON A.id = B.id
LEFT JOIN TableC C
       ON A.keyA = C.id

OUTPUT

|     Id | keyA | valor | joinkey | valor |
|--------|------|-------|---------|-------|
|      1 |   10 |    90 |       1 |   200 |
|      2 |   20 |    91 |       2 |   201 |
|      3 |   30 |    92 |       3 |   202 |
| (null) |   40 |    93 |      40 |   503 |

Answer 2

You can conditionally JOIN using OR logic like so:

CREATE TABLE #temp1 ( foo INT );
CREATE TABLE #temp2 ( bar INT );

INSERT  INTO #temp1
        ( foo )
VALUES  ( 1 ),( 2 ),( NULL ),( 4 ),( 5 )

INSERT  INTO #temp2
        ( bar ) 
VALUES  ( 1 ),( 2 ),( 3 ),( 4 ),( 5 ),( 99 )

SELECT  *
FROM    #temp1
INNER JOIN #temp2 ON ( foo = bar AND foo IS NOT NULL)
                        OR ( foo IS NULL AND bar = 99 )


DROP TABLE #temp1
DROP TABLE #temp2

It's not a great example, but I want to JOIN any NULL value to 99 to produce:

foo   bar
1     1
2     2
NULL  99
4     4
5     5

Hopefully you can follow the simplified logic to apply it to your problem.