从其他表格中获取所有细节类型的记录

Question

我有两个表格，Table1包含主记录，Table2包含详细信息。 我怎样才能获得的所有记录在Table1击中所有细节Table2

CREATE Table1 ([ID] INT , [Title] VARCHAR(256)) 
CREATE Table2 ([ID] INT, [Table1_ID] INT, [Detail] INT) 

样品：

Table1: 
ID Title 
--------------- 
1 Data_1 
2 Data_2 
3 Data_3 


Table2 
ID Table1_ID  Detail 
------------------------------- 
1  1    500 
2  1    600 
3  2    500 
4  3    500 
5  3    600 

我需要这样的结果：

Result: 
Table1_ID Table1_Title 
---------------------- 
1    Data_1 
3    Data_3 

我正在寻找一个最佳性能的方式。

Answer 1

我发现这个解决方案我不知道这是否是最好的方法来做到这一点？

DECLARE @T1 TABLE ([ID] INT, [Title] VARCHAR(256)); 
DECLARE @T2 TABLE ([ID] INT, [Table1_ID] INT, [Detail] INT); 

INSERT INTO @T1 
VALUES 
       (1, 'Data_1'), 
       (2, 'Data_2'), 
       (3, 'Data_3'); 

INSERT INTO @T2 
VALUES 
       (1, 1, 500), 
       (2, 1, 600), 
       (3, 2, 500), 
       (4, 3, 500), 
       (5, 3, 600); 


DECLARE @DetailCount INT 
SELECT @DetailCount = COUNT(DISTINCT [Detail]) FROM @T2 

SELECT 
     T1.[ID] [Table1_ID], 
     T1.[Title] [Table1_Title] 
FROM 
     @T1 T1 INNER JOIN 
     @T2 T2 ON T1.[ID] = T2.[Table1_ID] 
GROUP BY 
     T1.[ID], 
     T1.[Title] 
HAVING 
     COUNT(T2.[Detail]) = @DetailCount 

Answer 2

这是基本的内连接例如：

select Table1.ID, Title 
from Table1 
inner join Table2 on Table1.ID = Table2.Table1_ID 

Answer 3

的INNER JOIN两个表将让你存在两个表中的结果，所以之间：

SELECT 
    T1.ID, T1.Title 
FROM Table1 T1 
INNER JOIN Table2 T2 ON T1.ID = T2.Table1_ID 

会让你的所有记录从表中得到的表1中的ID在表2中

有一个辉煌的帖子HERE它解释了不同类型的联接

Answer 4

在这里你去：

DECLARE @T1 TABLE (ID INT, Title VARCHAR(256)); 
DECLARE @T2 TABLE (ID INT, Table1_ID INT, Detail INT); 

INSERT INTO @T1 VALUES 
(1,'Data_1'), 
(2,'Data_2'), 
(3,'Data_3'); 

INSERT INTO @T2 VALUES 
(1,1,500), 
(2,1,600), 
(3,2,500), 
(4,3,500), 
(6,3,600); 

SELECT T1.ID Table1_ID, T1.Title Table1_Title 
FROM @T1 T1 INNER JOIN @T2 T2 ON T1.ID = T2.ID 
WHERE T1.ID IN (1,3); 

输出：

+-----------+--------------+ 
| Table1_ID | Table1_Title | 
+-----------+--------------+ 
|   1 | Data_1  | 
|   3 | Data_3  | 
+-----------+--------------+ 

Demo。