Mysql查询，每行有多个子查询

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.

我有两个表需要从中提取数据。第一个表是应用程序,第二个表是verification_status。每个应用程序有20个步骤,validation_status包含该步骤完成的日期。我试图从一些具体步骤拉出应用程序以及完成日期。

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.

基本上我想要的是包含所有应用程序数据的行,以及基于applications_id和verification_step_id匹配的特定验证状态行的日期。

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

我试过这个,我收到一个错误,在'where子句'中显示“未知列'applications.id'”

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

3 个解决方案

#1

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;

#2

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.

我不确定这是问题所在,但您似乎在查询中混合了ANSI89和ANSI92语法。我从未写过将两者混合在一起的工作查询。此外,似乎您运行的子查询应该针对每行数据运行,而不是作为表运行。

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

#3

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

#1