CODEWITHSUNDEEP
mysqlnestedsubquery
IkouKuhn
IkouKuhn

Reputation: 89

Nested select from different tables

I'm trying to do a nested select but with different tables. Here's my query:

SELECT contact_profile.name, main_app.fk_lkp_app, main_app.id as main_id,
(
    -- if the main_app.fk_lkp_app value is 1 then do this
    SELECT SUM(translation_app.amount)
    FROM translation_app
    WHERE translation_app.fk_main_app = main_app.id 
    AND translation_app.status = 2 
    AND main_app.srf_number is not null 
    AND main_app.fk_invoice is not null

    -- if the main_app.fk_lkp_app value is 2 then do this
    SELECT SUM(interpretation_app.amount)
    FROM interpretation_app
    WHERE interpretation_app.fk_main_app = main_app.id 
    AND interpretation_app.status =2 
    AND main_app.srf_number is not null 
    AND main_app.fk_invoice is not null

    -- if the main_app.fk_lkp_app value is 3 then do this
    SELECT SUM(course_app.amount)
    FROM course_app
    WHERE course_app.fk_main_app = main_app.id
    AND course_app.status =2
    AND main_app.srf_number is not null 
    AND main_app.fk_invoice is not null
) as amount
FROM contact_profile
LEFT JOIN main_app ON main_app.fk_contact_profile = contact_profile.id 
WHERE main_app.fk_lkp_app in (1,2,3) 
AND main_app.srf_number is not null 
AND main_app.fk_invoice is not null 
GROUP BY contact_profile.name
ORDER BY amount DESC

As you can see, the "amount" field is selected from different tables based on main_app.fk_lkp_app value. The problem is how is the best way to do this query? I'm stuck with the "main_app.fk_lkp_app" value parameter part.

I even tried using CASE as suggested but it keeps giving me error code #1064

SELECT contact_profile.name, main_app.fk_lkp_app, main_app.id as main_id,
(
CASE 
    WHEN main_app.fk_lkp_app = '1' 
    THEN (  
        SELECT SUM(translation_app.amount)
        FROM translation_app
        WHERE translation_app.fk_main_app = main_app.id 
    )
    WHEN main_app.fk_lkp_app = '2' 
    THEN (
        SELECT SUM(interpretation_app.amount)
        FROM interpretation_app
        WHERE interpretation_app.fk_main_app = main_app.id 
    )
    WHEN main_app.fk_lkp_app = '3' 
    THEN (
        SELECT SUM(course_app.amount)
        FROM course_app
        WHERE course_app.fk_main_app = main_app.id
    )
    ELSE 0
END CASE
) as amount
FROM contact_profile
LEFT JOIN main_app ON main_app.fk_contact_profile = contact_profile.id 
WHERE main_app.fk_lkp_app in (1,2,3) 
AND main_app.srf_number is not null 
AND main_app.fk_invoice is not null 
GROUP BY contact_profile.name
ORDER BY amount DESC

The weird thing is if I don't use case and only use 1 select from the 3 variant(for example I only select from the translation_app table) the query is working.

Upvotes: 0

Views: 89

Answers (1)

George Pant
George Pant

Reputation: 2117

You could check the value of main_app.fk_lkp using a CASE expression

http://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html#operator_case

and perform a query based on that value.I could not test it but something like this should work

SELECT contact_profile.name, main_app.fk_lkp_app, main_app.id as main_id,
CASE
    WHEN  main_app.fk_lkp_app = 1 THEN (/* your query here */)
    WHEN  main_app.fk_lkp_app = 2 THEN (/* your query here */)
    WHEN  main_app.fk_lkp_app = 2 THEN (/* your query here */)
    ELSE 0
END AS amount ....

Upvotes: 1

Related Questions