Mysql query with multiple sub queries for each row

Question

I have two tables that I need to pull data from. The first table is applications and the second is verification_status. Each application has 20 steps and the verification_status contains a date of when that step was completed. I am trying to pull the application along with the completion dates from a number of specific steps.

Essentially what I want is a row that has all the applications data, along with the dates from the specific verification status rows matched based on applications_id and verification_step_id.

I tried this and I am getting an error that says "Unknown column 'applications.id' in 'where clause'"

It works fine when I take out the subquery.

SELECT * FROM applications,
(SELECT date FROM verification_status WHERE application_id = applications.id AND verification_step_id = 1) as steponedate
LEFT JOIN application_details ON application_details.application_id = applications.id
WHERE application_details.application_status_id != 1 AND application_details.application_status_id != 2

freeman_irl · Accepted Answer · 2013-09-18 13:43:09Z

2

Give this a go:

SELECT
  a.*,
  ad.*,
  vs.date as steponedate
FROM applications a
  LEFT JOIN verification_status vs
    ON vs.application_id = a.id
    AND vs.verification_step_id = 1
  LEFT JOIN application_details ad
    ON ad.application_id = a.id
WHERE ad.application_status_id != 1
  AND ad.application_status_id != 2;

answered Sep 18, 2013 at 13:43

freeman_irl

1581 silver badge10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Fluffeh · Accepted Answer · 2013-09-18 12:58:27Z

I am not certain that this is the problem, but you seem to be mixing ANSI89 and ANSI92 syntax in your query. I have never written a working query mixing the two together. Also, it seems as if the subquery you ran was supposed to be run for each row of data, not as a table.

I think the following query will likely work for you:

SELECT 
    applications.*, 
    (
        SELECT 
            date 
        FROM 
            verification_status 
        WHERE 
            application_id = applications.id 
            AND verification_step_id = 1
    ) as steponedate
FROM 
    applications,
    LEFT JOIN 
        application_details 
            ON application_details.application_id = applications.id
WHERE 
    application_details.application_status_id != 1 
    AND application_details.application_status_id != 2

Odward · Accepted Answer · 2013-09-18 13:05:00Z

0

Try to declare the application table in this subrequest :

SELECT date FROM verification_status,applications WHERE application_id = applications.id AND verification_step_id = 1

answered Sep 18, 2013 at 13:05

Odward

1131 silver badge9 bronze badges

Collectives™ on Stack Overflow

Mysql query with multiple sub queries for each row

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related