Sub Query Help

1

CREATE TABLE table1
(
Email VARCHAR(50),
Cus_id INT
)

INSERT INTO dbo.table1
( Email, Cus_id )
Select'abc@hotmail.com',456
UNION
SELECT 'abc@hotmail.com',923
UNION
SELECT 'james@yahoo.com',1002
UNION
SELECT 'james@yahoo.com',1009
UNION
Select'Smith@hotmail.com',456
UNION
SELECT 'abc@hotmail.com',123

SELECT * FROM dbo.table1

--Creating Table2

CREATE TABLE table2
(
LinkingKey INT,
Cus_id INT
)

INSERT INTO dbo.table2
( LinkingKey, Cus_id )
SELECT '928574','456'
UNION
SELECT '92589','923'
UNION
SELECT '10023','1002'
UNION
SELECT '10045','1009'

SELECT * FROM dbo.table1
SELECT * FROM dbo.table2

SELECT
MAX(Cus_id) AS Max_Cus_ID
,MIN(Cus_id) AS Min_Cus_ID
INTO #TEMP1
FROM dbo.table1
GROUP BY Email
ORDER BY Email

--Now this table is ready to Link to TABLE2
SELECT
T2.LinkingKey AS Max_K_Key
FROM dbo.table2 T2
INNER JOIN #TEMP1 T ON T2.Cus_id = T.Max_Cus_ID

Note:- I got the MAX KEY, how Can I get the MIN KEY? See below sample that I want to be my end result. It would be
great IF I can avoid TO CREATE a temp TABLE AS well
--Here is the final result that I want

Max_K_Key,Min_M_Key
10045,10023

2 
SELECT T1.LinkingKey AS Max_K_Key
      ,T2.LinkingKey AS Min_K_Key
  FROM #TEMP1 T
       INNER JOIN dbo.table2 T1
               ON T1.Cus_id=T.Max_Cus_ID
       INNER JOIN dbo.table2 T2
               ON T2.Cus_id=T.Min_Cus_ID
1 Like
3

Thanks Bitsmed. Awesome that's what I want.

--Answered****