Merging two select cases

Prev: Insert trigger
Next: xml.value xquery on element centric xml parameter

From: mpaine on 1 Dec 2009 22:31

---

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

From: Plamen Ratchev on 1 Dec 2009 23:08

---

It is unclear what is the final result set that you expect (you note to merge both queries with additional condition to
avoid PR_ID for those rows the have USER_ID, but how that logic is defined is not clear).

See if the following will give you some ideas:

SELECT ID, PR_ID, [USER_ID]
FROM (

SELECT ID, PR_ID, [USER_ID]
FROM (
SELECT ID, PR_ID, [USER_ID] FROM #A a
UNION
SELECT ID, PR_ID, [USER_ID] FROM #B
WHERE [User_ID] IS NOT NULL) AS A

UNION

SELECT ID, PR_ID, [USER_ID]
FROM (
SELECT ID, PR_ID, [USER_ID] FROM #C
UNION
SELECT ID, PR_ID, [USER_ID] FROM #D
WHERE [User_ID] IS NULL

EXCEPT

SELECT ID, PR_ID, NULL FROM #B
WHERE [User_ID] IS NOT NULL) AS B) AS T;

--
Plamen Ratchev
http://www.SQLStudio.com

From: mpaine on 2 Dec 2009 01:48

---

Thanks for the response. Here is another version of the dataset I'm working
with:



DROP TABLE #A; DROP TABLE #C;

CREATE TABLE #A (ID int, PR_ID int null, [User_ID] int null, Job_Type_ID int
null, Medium_ID int null)
INSERT INTO #A (ID, PR_ID, [USER_ID], Job_Type_ID, Medium_ID) VALUES
(99, NULL, 904551, 30, 1)
INSERT INTO #A (ID, PR_ID, [USER_ID], Job_Type_ID, Medium_ID) VALUES
(99, NULL, 904551, 30, 3)
INSERT INTO #A (ID, PR_ID, [USER_ID], Job_Type_ID, Medium_ID) VALUES
(96, 57, NULL, 30, 1)
INSERT INTO #A (ID, PR_ID, [USER_ID], Job_Type_ID, Medium_ID) VALUES
(96, 57, NULL, 30, 3)

CREATE TABLE #C (ID int, PR_ID int null, [User_ID] int null, Job_Type_ID int
null, Medium_ID int null)
INSERT INTO #C (ID, PR_ID, [USER_ID], Job_Type_ID, Medium_ID) VALUES
(56, 57, NULL, 7, 1)
INSERT INTO #C (ID, PR_ID, [USER_ID], Job_Type_ID, Medium_ID) VALUES
(56, 57, NULL, 7, 2)
INSERT INTO #C (ID, PR_ID, [USER_ID], Job_Type_ID, Medium_ID) VALUES
(56, 57, NULL, 7, 3)

SELECT * FROM #A -- returns 4 rows but I would like it to only return rows
with ID=99 and not ID=96 (since ID 96 and 99 have same Job_Type_ID and
Medium_ID)

SELECT * FROM #C -- returns the correct data set

--
msdn premium subscriber


"Plamen Ratchev" wrote:

> It is unclear what is the final result set that you expect (you note to merge both queries with additional condition to
> avoid PR_ID for those rows the have USER_ID, but how that logic is defined is not clear).
>
> See if the following will give you some ideas:
>
> SELECT ID, PR_ID, [USER_ID]
> FROM (
>
> SELECT ID, PR_ID, [USER_ID]
> FROM (
> SELECT ID, PR_ID, [USER_ID] FROM #A a
> UNION
> SELECT ID, PR_ID, [USER_ID] FROM #B
> WHERE [User_ID] IS NOT NULL) AS A
>
> UNION
>
> SELECT ID, PR_ID, [USER_ID]
> FROM (
> SELECT ID, PR_ID, [USER_ID] FROM #C
> UNION
> SELECT ID, PR_ID, [USER_ID] FROM #D
> WHERE [User_ID] IS NULL
>
> EXCEPT
>
> SELECT ID, PR_ID, NULL FROM #B
> WHERE [User_ID] IS NOT NULL) AS B) AS T;
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com
> .
>

From: Tom Cooper on 2 Dec 2009 02:38

---

I'm not sure I understand what you want. But perhaps

With cte As
(Select ID,PR_ID,USER_ID,Job_Type_ID,Medium_ID,
Row_Number() Over (Partition By Job_Type_ID, Medium_ID Order By Case When
User_ID is Not Null Then 1 Else 2 End) As rn
From #A)
Select ID, PR_ID, USER_ID, Job_Type_ID, Medium_ID
From cte
Where rn = 1;

Tom

"mpaine" <mpaine(a)community.nospam> wrote in message
news:7820F6FC-A01C-4951-B5E5-133FB2FC3A07(a)microsoft.com...
> Thanks for the response. Here is another version of the dataset I'm
> working
> with:
>
>
>
> DROP TABLE #A; DROP TABLE #C;
>
> CREATE TABLE #A (ID int, PR_ID int null, [User_ID] int null, Job_Type_ID
> int
> null, Medium_ID int null)
> INSERT INTO #A (ID, PR_ID, [USER_ID], Job_Type_ID, Medium_ID) VALUES
> (99, NULL, 904551, 30, 1)
> INSERT INTO #A (ID, PR_ID, [USER_ID], Job_Type_ID, Medium_ID) VALUES
> (99, NULL, 904551, 30, 3)
> INSERT INTO #A (ID, PR_ID, [USER_ID], Job_Type_ID, Medium_ID) VALUES
> (96, 57, NULL, 30, 1)
> INSERT INTO #A (ID, PR_ID, [USER_ID], Job_Type_ID, Medium_ID) VALUES
> (96, 57, NULL, 30, 3)
>
> CREATE TABLE #C (ID int, PR_ID int null, [User_ID] int null, Job_Type_ID
> int
> null, Medium_ID int null)
> INSERT INTO #C (ID, PR_ID, [USER_ID], Job_Type_ID, Medium_ID) VALUES
> (56, 57, NULL, 7, 1)
> INSERT INTO #C (ID, PR_ID, [USER_ID], Job_Type_ID, Medium_ID) VALUES
> (56, 57, NULL, 7, 2)
> INSERT INTO #C (ID, PR_ID, [USER_ID], Job_Type_ID, Medium_ID) VALUES
> (56, 57, NULL, 7, 3)
>
> SELECT * FROM #A -- returns 4 rows but I would like it to only return
> rows
> with ID=99 and not ID=96 (since ID 96 and 99 have same Job_Type_ID and
> Medium_ID)
>
> SELECT * FROM #C -- returns the correct data set
>
> --
> msdn premium subscriber
>
>
> "Plamen Ratchev" wrote:
>
>> It is unclear what is the final result set that you expect (you note to
>> merge both queries with additional condition to
>> avoid PR_ID for those rows the have USER_ID, but how that logic is
>> defined is not clear).
>>
>> See if the following will give you some ideas:
>>
>> SELECT ID, PR_ID, [USER_ID]
>> FROM (
>>
>> SELECT ID, PR_ID, [USER_ID]
>> FROM (
>> SELECT ID, PR_ID, [USER_ID] FROM #A a
>> UNION
>> SELECT ID, PR_ID, [USER_ID] FROM #B
>> WHERE [User_ID] IS NOT NULL) AS A
>>
>> UNION
>>
>> SELECT ID, PR_ID, [USER_ID]
>> FROM (
>> SELECT ID, PR_ID, [USER_ID] FROM #C
>> UNION
>> SELECT ID, PR_ID, [USER_ID] FROM #D
>> WHERE [User_ID] IS NULL
>>
>> EXCEPT
>>
>> SELECT ID, PR_ID, NULL FROM #B
>> WHERE [User_ID] IS NOT NULL) AS B) AS T;
>>
>> --
>> Plamen Ratchev
>> http://www.SQLStudio.com
>> .
>>

From: Justin Blanding on 2 Dec 2009 10:19

---

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

 |  Next  |  Last
Pages: 1 2
Prev: Insert trigger
Next: xml.value xquery on element centric xml parameter