即使第二个表没有相应的条目，也能从 2 个 db 表中获取查询结果 [英] Getting query results from 2 db tables even when second table has no corresponding entries

问题描述

这是我的问题:即使第二个表没有相应的条目，也从 2 个数据库表中获取查询结果.

Here is my problem: Getting query results from 2 db tables even when second table has no corresponding entries.

这是我在 SQL 中使用的一些示例代码

Here is some sample code that i use in SQL

SELECT DISTINCT apps.id, apps.*, req.status
FROM applications AS apps, requests AS req
WHERE apps.id = {$app_id}

但问题是它不会提取没有 request.status 值/条目的应用程序，所以问题是:是否可以修改这个简单的查询以提取所有应用程序的结果.* 行即使没有相应的 requests.status 行/条目?

But the issue is that it will not pull up apps that do not have a request.status value/entry, so the questions is: Would it be posible to modify this simple Query to pull up results for all application.* rows even when there is no corresponding requests.status row/entry?

编辑波纹管:

这里是我收到的反馈中的新查询(这很好用)

so here is my new query from the feedback that i got (this works fine)

SELECT DISTINCT 
    apps.*, req.status
FROM 
    applications AS apps
    LEFT JOIN requests AS req ON (req.app_id = apps.id AND req.uid = {$user_id})
WHERE 
    apps.id = {$app_id}

但是:当我在 where 子句中添加一个新表达式来过滤请求状态时，我遇到了与隐式查询相同的问题，我没有得到结果(查询如下)

BUT: When i add a new expression to the where clause to filter the request status, i get the same issue as with implicit query, i don't get results (the query is bellow)

SELECT DISTINCT 
    apps.*, req.status
FROM 
    applications AS apps
    LEFT JOIN requests AS req ON (req.app_id = apps.id AND req.uid = {$user_id})
WHERE 
    apps.id = {$app_id}
AND
    (req.status = 2 OR req.status = 5)

另一个编辑

这是要查看的更新代码.现在的问题是，如果我添加一个子查询，子查询将拉出所有行，然后将 NULL 作为状态列的值，但是一旦我添加了 WHERE status != 2 它只会删除所有条目，这不应该还有所有的行都是空的吗?因为 null 显然是 != 2.

Here is updated code to look at. The issue now is that if i add a subquery, the sub query will pull up all the rows, then have NULL as value for the status column, but as soon as i add a WHERE status != 2 it just removes all the entries, shouldn't this still have all the rows with null? because null is clearly != 2.

SELECT DISTINCT
    apps . *
FROM
    (SELECT 
        apps . *, req.status
    FROM
        appstore_app AS apps
    LEFT JOIN app_user_request AS req ON (req.uid = 187 AND req.appid = apps.appid)
    WHERE
        apps.appid > 0 AND apps.company_id = 122) AS apps
WHERE
    apps.status != 2
ORDER BY apps.average_user_rating DESC

最终编辑

感谢所有帮助！

这是对我有用的最后一个查询:

Here is my final query that worked for me:

SELECT 
    apps.*, req.status
FROM
    appstore_app AS apps
        LEFT JOIN
    app_user_request AS req ON (req.uid = {$user_id} AND req.appid = apps.appid AND (req.status IS NULL OR req.status != 2))
WHERE
    apps.appid > 0 AND apps.company_id = {$company_id}

推荐答案

O.P.说:

但是: 当我在 where 子句中添加一个新表达式来过滤请求状态时，我遇到了与隐式查询相同的问题，我没有得到结果(查询如下)

BUT: When i add a new expression to the where clause to filter the request status, i > get the same issue as with implicit query, i don't get results (the query is bellow)

SELECT DISTINCT 
       apps.*,
       req.status
FROM applications  apps
LEFT JOIN requests req ON req.app_id = apps.id
                      AND req.uid    = {$user_id}
WHERE apps.id = {$app_id}
  AND (    req.status = 2
        OR req.status = 5
      )

问题在于 where 子句过滤了结果集，因此您对 req.status 为 2 或 5 的测试会抛出 req.status 为空，因为没有与表 applications 匹配的行.

The problem is that the where clause filters the results set, so your test for a req.status of 2 or 5 throws out anything where req.status is null because no row matched the table applications.

select 语句的一般理论上的操作顺序(因为只有一个微不足道的实现才会真正做这样的事情):

The general, theoretical (since nothing but a trivial implementation would ever actually do anything like this) order of operations for a select statement is:

• 生成 from 子句中列出的每个表的完整笛卡尔积.
• 通过应用指定的 join 条件过滤掉那些没有通过指定测试的行.
• 应用 where 子句中指定的过滤条件，删除未通过指定测试的行.
• 对 group by 子句中指定的表达式的结果集进行排序，并将结果集划分为组.
• 将每个这样的组折叠成一行，计算所有指定聚合函数的值.
• 从结果集中删除所有未在 select 语句列列表中列出的列.
• 按照 order by 子句中指定的列/表达式对这个最终结果集进行排序.

• Produce the full cartesian product of each table listed in the from clause.
• Filter that by applying the specified join criteria and eliminated rows that don't pass the specified tests.
• Apply the filter criteria specified in the where clause, removing rows that don't pass the specified tests.
• Order the results set on the expressions specified in the group by clause and partitition the results set into groups.
• Collapse each such group into a single row, computing the value of all specified aggregate functions.
• Remove all columns from the results set that aren't listed in the select statements column list.
• Order this final results set by the columns/expressions specified in the order by clause.

您可以执行以下两种操作之一:

You can do one of two things:

• 更改您的查询以测试无效性:

• change your query to test for nullity:

where...( req.status is null OR req.status in (2,5) )...

将针对 req.status 的测试移至加入条件:

move the test against on req.status to the join criteria:

left join requests req on req.app_id =  apps.id
                      and req.uid    =  {$user_id}
                      and req.status in (2,5)

这篇关于即使第二个表没有相应的条目，也能从 2 个 db 表中获取查询结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！