CODEWITHSUNDEEP
mysqlsql
user1862764
user1862764

Reputation: 389

refrence parent field from sub inner join select

I have this query that select records from a table and LEFT JOINS other two tables.

Here's the query

SELECT mainTest.lab_number_auto, mainTest.lab_number,
SUM(CASE WHEN test.lab_number = mainTest.lab_number
    THEN test.test_unit_cost ELSE 0 END) cost,
payment.totalPaid

FROM patient_main_test mainTest

LEFT JOIN patient_test test    
ON test.lab_number = mainTest.lab_number 

LEFT JOIN (
    SELECT SUM(CASE WHEN testpayment.lab_number = mainTest.lab_number THEN testpayment.amount_paid ELSE 0 END) totalPaid
    FROM patient_test_payment testpayment
    GROUP BY testpayment.lab_number
    ) AS payment
ON payment.lab_number = mainTest.lab_number 

WHERE mainTest.lab_number != ''
GROUP BY mainTest.lab_number

On running this query i get this error

MySQL said:
#1054 - Unknown column 'mainTest.lab_number' in 'field list'

Thanks.

Here's a fiddle i created. http://sqlfiddle.com/#!2/291c4/8

Upvotes: 0

Views: 176

Answers (1)

G B
G B

Reputation: 1462

Try this, if im right the issue is that you were trying to treat your derived table in the left join like a co-related sub-query. & thx for the sql fiddle... makes it so much easier to help you.

SELECT mainTest.lab_number_auto, mainTest.lab_number,
SUM(CASE WHEN test.lab_number = mainTest.lab_number
    THEN test.test_unit_cost ELSE 0 END) cost,
(
    SELECT SUM(CASE WHEN testpayment.lab_number = mainTest.lab_number THEN testpayment.amount_paid ELSE 0 END) totalPaid
    FROM patient_test_payment testpayment
    Where lab_number = mainTest.lab_number
    GROUP BY testpayment.lab_number
    ) AS totalPaid

FROM patient_main_test mainTest

LEFT JOIN patient_test test    
ON test.lab_number = mainTest.lab_number 

WHERE mainTest.lab_number != ''
GROUP BY mainTest.lab_number

Upvotes: 2

Related Questions