May 26, 2017 at 1:38 pm
Here the sample code'
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
(
LinkingKeyINT,
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
May 26, 2017 at 2:05 pm
.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
May 26, 2017 at 2:15 pm
I think I understand what you're doing, you should just need another join back to table2:
SELECT T2max.LinkingKey AS Max_K_Key, T2min.LinkingKey AS Min_K_Key
FROM #TEMP1 T
INNER JOIN dbo.table2 T2max ON T.Max_Cus_ID = T2max.Cus_id
INNER JOIN dbo.table2 T2min ON T.Min_Cus_ID = T2min.Cus_id
May 26, 2017 at 4:31 pm
Awesome! That's what I need.
Thank You for your help!
--*******This Question is answered by Chris*************
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply