How to write this query?

Question

Post reply

How to write this query?

water490

Ten Centuries

Points: 1083

November 14, 2024 at 5:27 am

Go to Answer

#4481465

Hi everyone

I have two tables storing stock prices. Each table comes from a different source so there are slight differences between them at times. I need to know what those differences are. I started the query and it works if I look at the results separately. I am looking at combining the results so that way I only have to run 1 query (vs the current 2).

Below is my query:

DROP TABLE IF EXISTS #STOCKS1
DROP TABLE IF EXISTS #STOCKS2

CREATE TABLE #STOCKS1
(
SYMBOL1 [nchar](10) NOT NULL
)
GO

CREATE TABLE #STOCKS2
(
SYMBOL2 [nchar](10) NOT NULL
)
GO

INSERT INTO #STOCKS1 
VALUES ('D'),('AA'),('ABC'),('A')

INSERT INTO #STOCKS2 
VALUES ('J'),('AA')

SELECT* FROM #STOCKS1
SELECT* FROM #STOCKS2

--RETURN SYMBOLS IN #STOCKS1 BUT NOT IN #STOCKS2
SELECT  *
FROM    #STOCKS1 AS T1 LEFT JOIN #STOCKS2 AS T2 ON T1.SYMBOL1 = T2.SYMBOL2
WHERE   T2.SYMBOL2 IS NULL
ORDER BY 1

--RETURN SYMBOLS IN #STOCKS2 BUT NOT IN #STOCKS1
SELECT  *
FROM    #STOCKS2 AS T1 LEFT JOIN #STOCKS1 AS T2 ON T1.SYMBOL2 = T2.SYMBOL1
WHERE   T2.SYMBOL1 IS NULL
ORDER BY 1

DROP TABLE #STOCKS1
DROP TABLE #STOCKS2

Below is desired outcome:

Screenshot 2024-11-13 212655

How can I modify my query so I get the desired outcome?

Thank you

ScottPletcher

SSC Guru

Points: 101192

November 14, 2024 at 3:18 pm

Answer

#4481771

Maybe?:

SELECT SYMBOL1, SYMBOL2
FROM (
    SELECT SYMBOL1, ROW_NUMBER() OVER(ORDER BY SYMBOL1) AS row_num_1
    FROM #STOCKS1 S1
    WHERE NOT EXISTS(SELECT * FROM #STOCKS2 S2 WHERE S2.SYMBOL2 = S1.SYMBOL1)
) AS S1
FULL OUTER JOIN (
    SELECT SYMBOL2, ROW_NUMBER() OVER(ORDER BY SYMBOL2) AS row_num_2
    FROM #STOCKS2 S2
    WHERE NOT EXISTS(SELECT * FROM #STOCKS1 S1 WHERE S1.SYMBOL1 = S2.SYMBOL2)
) AS S2 ON S2.row_num_2 = S1.row_num_1
ORDER BY 1, 2

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

water490 Ten Centuries Points: 1083 More actions · Answer 1

I made a bit of progress. The format doesn't quite match the desired outcome. What can I do to fix it so it does match the desired outcome?

DROP TABLE IF EXISTS #STOCKS1
DROP TABLE IF EXISTS #STOCKS2
DROP TABLE IF EXISTS #TEMP1
DROP TABLE IF EXISTS #TEMP2

CREATE TABLE #STOCKS1
(
SYMBOL1 [nchar](10) NOT NULL
)
GO

CREATE TABLE #STOCKS2
(
SYMBOL2 [nchar](10) NOT NULL
)
GO

INSERT INTO #STOCKS1 
VALUES ('D'),('AA'),('ABC'),('A')

INSERT INTO #STOCKS2 
VALUES ('J'),('AA')

SELECT * FROM #STOCKS1
SELECT * FROM #STOCKS2

--RETURN SYMBOLS IN #STOCKS1 BUT NOT IN #STOCKS2
SELECT *
INTO #TEMP1
FROM #STOCKS1 AS T1 LEFT JOIN #STOCKS2 AS T2 ON T1.SYMBOL1 = T2.SYMBOL2
WHERE T2.SYMBOL2 IS NULL
ORDER BY 1

--RETURN SYMBOLS IN #STOCKS2 BUT NOT IN #STOCKS1
SELECT  *
INTO  #TEMP2
FROM  #STOCKS2 AS T1 LEFT JOIN #STOCKS1 AS T2 ON T1.SYMBOL2 = T2.SYMBOL1
WHERE  T2.SYMBOL1 IS NULL
ORDER BY 1

--RETURN RESULTS
SELECT  SYMBOL1 AS IN_STOCKS1_NOT_IN_STOCKS2,
SYMBOL2 AS IN_STOCKS2_NOT_IN_STOCKS1
FROM  #TEMP1
UNION ALL
SELECT SYMBOL1,
SYMBOL2
FROM #TEMP2

DROP TABLE #STOCKS1
DROP TABLE #STOCKS2
DROP TABLE #TEMP1
DROP TABLE #TEMP2

DesNorton SSC-Insane Points: 24516 More actions · Answer 2

This is an alternate piece of code that will match your 2nd attempt

SELECT [IN_1_NOT_IN_2] = s1.SYMBOL
     , [IN_2_NOT_IN_1] = s2.SYMBOL
FROM (
      --RETURN SYMBOLS IN #STOCKS1 BUT NOT IN #STOCKS2
      SELECT [SYMBOL]=[SYMBOL1] FROM #STOCKS1
      EXCEPT
      SELECT [SYMBOL]=[SYMBOL2] FROM #STOCKS2
     ) AS s1
FULL OUTER JOIN (
      --RETURN SYMBOLS IN #STOCKS2 BUT NOT IN #STOCKS1
      SELECT [SYMBOL]=[SYMBOL2] FROM #STOCKS2
      EXCEPT
      SELECT [SYMBOL]=[SYMBOL1] FROM #STOCKS1
     ) AS s2 ON s1.SYMBOL = s2.SYMBOL

How to post data/code on a forum to get the best help.

water490 Ten Centuries Points: 1083 More actions · Answer 3

ScottPletcher wrote:

Maybe?:

SELECT SYMBOL1, SYMBOL2
FROM (
    SELECT SYMBOL1, ROW_NUMBER() OVER(ORDER BY SYMBOL1) AS row_num_1
    FROM #STOCKS1 S1
    WHERE NOT EXISTS(SELECT * FROM #STOCKS2 S2 WHERE S2.SYMBOL2 = S1.SYMBOL1)
) AS S1
FULL OUTER JOIN (
    SELECT SYMBOL2, ROW_NUMBER() OVER(ORDER BY SYMBOL2) AS row_num_2
    FROM #STOCKS2 S2
    WHERE NOT EXISTS(SELECT * FROM #STOCKS1 S1 WHERE S1.SYMBOL1 = S2.SYMBOL2)
) AS S2 ON S2.row_num_2 = S1.row_num_1
ORDER BY 1, 2

Thank you so much. This works!

Jonathan AC Roberts SSCoach Points: 18407 More actions · Answer 4

Not quite the same result but has all the info:

SELECT *
  FROM #STOCKS1 AS T1 
  FULL JOIN #STOCKS2 AS T2 
         ON T1.SYMBOL1 = T2.SYMBOL2
 WHERE (T2.SYMBOL2 IS NULL
        OR T1.SYMBOL1 IS NULL)
 ORDER BY 1,2
;

DROP TABLE IF EXISTS #STOCKS1, #STOCKS2, #TEMP1, #TEMP2;

Also, you can "drop table if exists" with multiple table names to do that bit in one line.

water490 Ten Centuries Points: 1083 More actions · Answer 5

Jonathan AC Roberts wrote:

Not quite the same result but has all the info:
SELECT *
  FROM #STOCKS1 AS T1 
  FULL JOIN #STOCKS2 AS T2 
         ON T1.SYMBOL1 = T2.SYMBOL2
 WHERE (T2.SYMBOL2 IS NULL
        OR T1.SYMBOL1 IS NULL)
 ORDER BY 1,2
;

DROP TABLE IF EXISTS #STOCKS1, #STOCKS2, #TEMP1, #TEMP2;
Also, you can "drop table if exists" with multiple table names to do that bit in one line.

Thank you!