将select语句中的a子查询重构为连接

I've got a table of products and a table of widgets.

我有一张产品表和一张小部件表。

A product is built from a number of widgets and I've got a linking table which shows which widgets are linked to which products.

一个产品是由许多小部件构建的，我有一个链接表，显示哪些小部件链接到哪些产品。

Widgets can be marked as expired, which means they are not eligible to be added to any new products.

小部件可以标记为过期，这意味着它们没有资格添加到任何新产品中。

Schema and sample data in the following sqlFiddle -

以下sqlFiddle中的模式和示例数据。

I want a query that shows all the eligible widgets for a particular product given the following rules:

我想要一个查询，显示给定以下规则的特定产品的所有符合条件的小部件:

A widget should not be shown if it is marked as expired.
如果标记为过期，则不应显示小部件。
As an exception to the above rule - If a widget is marked as expired, but is already linked to a product, then it should be shown.
作为上述规则的一个例外——如果一个小部件被标记为过期，但是已经链接到一个产品，那么它应该被显示出来。
If a widget has already been linked to the product, it should be marked as 'selected'
如果一个小部件已经链接到该产品，那么它应该被标记为“selected”

I've got the following query which works, and satisfies all the above rules:

我有下面的查询，它是有效的，并且满足以上所有的规则:

select 
    data.WidgetName, 
    data.expired,
    case 
        when data.ID in (select data_id from Widget_link where productId = 1)   
        then 1 
        else 0 
    end as selected
from Widget_data data
left outer join Widget_link link
    on data.ID = link.data_id and data.expired = 1
where (data.expired = 0 or (data.expired = 1 and link.productId = 1))

I'd like to find a way to refactor the sub-query within the select part of the query to some kind of join. I'm trying to create a view which I can filter using just a where clause, rather than having the productId in two places. Is this possible?

我想找到一种方法，将查询的select部分中的子查询重构为某种连接。我试图创建一个视图，我可以使用where子句进行过滤，而不是在两个地方使用productId。这是可能的吗?

2 个解决方案

#1

On simplification your query can solely be evaluated based on JOIN like below, and there is no need for inner query at all.

通过简化，您的查询可以仅基于如下所示的连接进行评估，并且根本不需要内部查询。

select data.WidgetName,
     case when link.id is not null then 1 else 0 end as selected
from Widget_data data
left outer join Widget_link link
    on data.ID = link.data_id and link.productId = 1
where data.expired=0 or link.id is not null

Demo SQL fiddle

演示SQL小提琴

#2

You can use SQL CTE (Common Table Expression) to cover the results from a SELECT list, and use in a single SQL statement

您可以使用SQL CTE(公共表表达式)来覆盖SELECT列表中的结果，并在单个SQL语句中使用。

You can even create SQL statements with multiple CTE's in your code

您甚至可以在代码中创建具有多个CTE的SQL语句

What I suggest is can be summarized visually as follows

我的建议可以直观的总结如下

WITH cte AS (
  SELECT ..... FROM .....
),
other_cte AS (
  SELECT ..... FROM .....
)
SELECT * 
FROM cte
INNER JOIN other_cte ON...

#1