Hello, I have a union where I need to select rows from the first query if the Car Status column is equal to "New". Otherwise, I need to select rows from the second query in the union which comes from a separate db and table if the Car Status column in the first query equals "Used". I was thinking of an outer query wrapper and maybe use case statements to evaluate row by row, but now sure. Not even sure if I actually need a union, CTE, etc. Suggestions appreciated. Example query I have so far:
Select * From ---probably need case logic here.
(
Select
CarColor,
CarName,
CarStatus
From Db1.CarTbl1
UNION ALL
Select
CarColor,
CarName,
null CarStatus
From Db2.CarTbl27
)D
February 8, 2024 at 6:37 pm
IF I understand the q correctly, then:
SELECT
ISNULL(t2.CarColor, t1.CarColor) AS CarColor,
t1.CarName,
t1.CarStatus
FROM Db1.CarTbl1 t1
LEFT OUTER JOIN Db2.CarTbl27 t2 ON t1.CarStatus = 'Used' AND t2.CarName = t1.CarName
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".
February 8, 2024 at 7:15 pm
Thanks for the reply, Scott. I oversimplified the query a bit so I could post it on the forum here, and think now that I left out important information in my ask, but based on the value of the Car Status column in the first query, I need to either return the row from the first query (CarStatus=New) OR if it hits a row in that same query where the Car Status column = Used, return the corresponding row from the second query, but not both in the query output.
Other important info I left out: between the two queries, we can join them in a composite join if we need to. So, fields exist between the 2 queries like CarID, CompanyID ..if we need to do some sort of conditional join.
Trying to do this with straight SQL without TSQL variables if I can.
Help appreciated thank you!
Sample data and results are almost always clearer than just a textual explanation. I hope this matches what you need:
SELECT
ISNULL(t2.CarColor, t1.CarColor) AS CarColor,
t1.CarName,
t1.CarStatus--,
--ISNULL(t2...., t1....) AS ...,
--...
FROM Db1.CarTbl1 t1
LEFT OUTER JOIN Db2.CarTbl27 t2 ON t1.CarStatus = 'Used' AND t2.CarName = t1.CarName
WHERE (t1.CarStatus = 'New' AND NOT EXISTS(SELECT * FROM Db1.CarTbl1 t1u WHERE t1u.CarName = t1.CarName AND t1u.CarStatus = 'Used')) OR
(t1.CarStatus = 'Used' /*AND EXISTS(SELECT * FROM Db2.CarTbl27 t2 WHERE t2.CarName = t1.CarName)*/)
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".
February 9, 2024 at 3:02 pm
Scott, thanks so much!
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy