IkouKuhn IkouKuhn - 4 months ago 20
MySQL Question

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.

Answer

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 ....