SQL从具有多个内部联接的2个表中进行选择

SQL 2008: I am trying to get data (ie merge) from two tables (TODO (T) and TODO_OPERATOR(Z)) which have the same fields but different data, and then use 2 inner joins on the merged data. Not sure how to do it. An provided @id parameter would be 001 for example. Do you merge the T and Z tables in a select first somehow, and then do the INNER JOINS?

SQL 2008:我试图从具有相同字段但不同数据的两个表(TODO(T)和TODO_OPERATOR(Z))获取数据(即合并),然后在合并数据上使用2个内部联接。不知道怎么做。例如,提供的@id参数将为001。首先以某种方式合并选择T和Z表,然后进行INNER JOINS?

SELECT
T.*, Z.*, TT.lookup_desc todo_type_desc, TS.lookup_desc status_desc
FROM TODO T
CROSS JOIN TODO_OPERATOR Z
INNER JOIN LOOKUP TT ON T.todo_type=TT.lookup_id, Z.todo_type=TT.lookup_id
INNER JOIN LOOKUP TS ON t.status=TS.lookup_id, z.status=TS.lookup_id
WHERE id=@id

Simplified table fields would be:

简化的表格字段为:

TODO and TODO_OPERATOR:

TODO和TODO_OPERATOR:

id, status, todo_type

id,status,todo_type

LOOKUP:

lookup_id, lookup_desc

Thanks

2 个解决方案

#1

SELECT

A.*

,TT.lookup_desc todo_type_desc

,TS.lookup_desc status_desc

FROM

(select id, status, todo_type from TODO UNION ALL select id, status, todo_type from TODO_OPERATOR) A

(从TODO UNION ALL中选择id,status,todo_type从TODO_OPERATOR中选择id,status,todo_type)A

INNER JOIN LOOKUP TT ON A.todo_type=TT.lookup_id

INNER JOIN LOOKUP TT ON A.todo_type = TT.lookup_id

INNER JOIN LOOKUP TS ON A.status=TS.lookup_id

INEN JOIN LOOKUP TS ON A.status = TS.lookup_id

WHERE id=@id

#2

SELECT  
T.*, Z.*, TT.lookup_desc todo_type_desc, TS.lookup_desc status_desc  
FROM TODO T  
INNER JOIN TODO_OPERATOR Z  
ON Z.todo_type = T.todo_type  
AND z.status = T.status
INNER JOIN LOOKUP TT ON T.todo_type=TT.lookup_id  
INNER JOIN LOOKUP TS ON T.status=TS.lookup_id  
WHERE id=@id

I am not sure if this helps you, the information you give is a bit minimal.

我不确定这对你有帮助,你提供的信息有点小。

#1