RE: Merging two select cases
- From: Justin Blanding <JustinBlanding@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 2 Dec 2009 07:19:01 -0800
Um, you aren't providing enough info but I am pretty sure adding a union
between the two lines would return the result you want.
Also, you do realize that the unions only apply the where clause to the #b
and #d selects right?
SELECT ID, PR_ID, [USER_ID] FROM #A a
UNION
SELECT ID, PR_ID, [USER_ID] FROM #B
WHERE [User_ID] IS NOT NULL
Union All
SELECT ID, PR_ID, [USER_ID] FROM #C
UNION
SELECT ID, PR_ID, [USER_ID] FROM #D
WHERE ([User_ID] IS NULL)
"mpaine" wrote:
.
I am trying to produce a single select statement which is true for two
separate cases (so I can reuse it within an IN expression.. which means no IF
statements). The answer is probably so simple I'll smack my head. Here is the
setup:
DROP TABLE #A; DROP TABLE #B; DROP TABLE #C; DROP TABLE #D;
CREATE TABLE #A (ID int primary key clustered, PR_ID int null, [User_ID] int
null)
INSERT INTO #A (ID, PR_ID, [User_ID]) VALUES (99, NULL, 904551)
CREATE TABLE #B (ID int primary key clustered, PR_ID int null, [User_ID] int
null)
INSERT INTO #B (ID, PR_ID, [User_ID]) VALUES (96, 57, NULL)
CREATE TABLE #C (ID int primary key clustered, PR_ID int null, [User_ID] int
null)
CREATE TABLE #D (ID int primary key clustered, PR_ID int null, [User_ID] int
null)
INSERT INTO #D (ID, PR_ID, [User_ID]) VALUES (56, 57, NULL)
SELECT ID, PR_ID, [USER_ID] FROM #A a
UNION
SELECT ID, PR_ID, [USER_ID] FROM #B
WHERE [User_ID] IS NOT NULL
SELECT ID, PR_ID, [USER_ID] FROM #C
UNION
SELECT ID, PR_ID, [USER_ID] FROM #D
WHERE ([User_ID] IS NULL)
Do you know of a way to merge both of these select statements? I am using
SQL Server 2008, so CTE, CROSS APPLY, etc are welcomed. Basically, I want the
rows with a non-null User_ID to be selected, have priority if you will. If no
User_ID is available but a PR_ID case is not null, go ahead and select that
instead (but never both).
THANK YOU!
--
msdn premium subscriber
- References:
- Merging two select cases
- From: mpaine
- Merging two select cases
- Prev by Date: Re: very slow view
- Next by Date: Re: Need help w/ MERGE updating datat from staging table
- Previous by thread: Re: Merging two select cases
- Next by thread: Re: Merging two select cases
- Index(es):
Relevant Pages
|
