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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply