SQL Queries for geting common values and uncommon values

Posted by Sankar20092010 under Sql Server on 3/15/2013 | Points: 10 | Views : 1605 | Status : [Member] | Replies : 4
Hi......

I have one table in my database say for example "TableA" and the data is shown below..

ID Value
1 A (common for all ids)
1 B (common for all ids)
2 A (common for all ids)
2 C
2 B (common for all ids)
3 A (common for all ids)
3 B (common for all ids)
3 C
3 D

My problem is i need two sql queries which will give the following two resultant sets as shown below

First sql query result will be like this Common Factors in ID (1,2,3)
A
B

Second query result will be Common Factors in ID (1,2,3)
C (here c is not in id 1 so will be listed here)
D

Can any one please give the two queries please , just i need common factors in one set and uncommon factors in another set ..I need two SQL queries but

Thanks and Regards

Sankar




Responses

Posted by: Pandians on: 3/17/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
Check It Out!

1. Sample Table and Data
CREATE TABLE Table1

(
[ID] INT ,
[Value] VARCHAR(5)
)
GO

INSERT [dbo].[Table1] ([ID], [Value]) VALUES (1, 'A')
INSERT [dbo].[Table1] ([ID], [Value]) VALUES (1, 'B')
INSERT [dbo].[Table1] ([ID], [Value]) VALUES (2, 'A')
INSERT [dbo].[Table1] ([ID], [Value]) VALUES (2, 'C')
INSERT [dbo].[Table1] ([ID], [Value]) VALUES (2, 'B')
INSERT [dbo].[Table1] ([ID], [Value]) VALUES (3, 'A')
INSERT [dbo].[Table1] ([ID], [Value]) VALUES (3, 'B')
INSERT [dbo].[Table1] ([ID], [Value]) VALUES (3, 'C')
INSERT [dbo].[Table1] ([ID], [Value]) VALUES (3, 'D')
GO
2. Script for Not Common
/*NOT COMMON*/

;WITH Combination
AS
(
SELECT DISTINCT A.ID, B.Value FROM Table1 A, Table1 B
), [Not Common]
AS
(
SELECT DISTINCT C.value [Not Common] FROM Combination C LEFT JOIN Table1 T1
ON (C.ID = T1.ID AND C.Value = T1.Value)
WHERE (T1.ID IS NULL AND T1.Value IS NULL)
)

SELECT [Not Common] FROM [Not Common]
GO
Not Common

----------
C
D
3. Script for Common
/*COMMON*/

;WITH Combination
AS
(
SELECT DISTINCT A.ID, B.Value FROM Table1 A, Table1 B
), [Not Common]
AS
(
SELECT DISTINCT C.value [Not Common] FROM Combination C LEFT JOIN Table1 T1
ON (C.ID = T1.ID AND C.Value = T1.Value)
WHERE (T1.ID IS NULL AND T1.Value IS NULL)
), [Common]
AS
(
SELECT T1.* FROM Table1 T1 WHERE NOT EXISTS(SELECT 1 FROM [Not Common] NC WHERE NC.[Not Common] = T1.Value)
)

SELECT DISTINCT Value [Common] FROM [Common]
GO
Common

----------
A
B


Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Sankar20092010, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jasminej on: 3/19/2013 [Member] Starter | Points: 25

Up
0
Down
Thanks for the logic!

Sankar20092010, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sankar20092010 on: 3/19/2013 [Member] Starter | Points: 25

Up
0
Down
Hi thanks but i have a column ID but it is nvarchar that is my main sql query has two columns as nvarchar

Sankar20092010, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pandians on: 3/19/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
If you are going to use the script then, You can simply change the Data Type of ID column to NVARCHAR instead of INT. It'll work fine!

Did you get change to try that ?

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Sankar20092010, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response