What is the SQL Contruct for returning records based on target conditions for the same Column.

Question

What is the SQL construct to return the records where doc_type equals all three conditions of A, B and C and excludes all the others.
Meaning in the example below that only the records for PD_ID 2901 and 2905 would be returned because they are the only ones that have all 3 values of A, B and C for the Doc_Type Column.

For the example Table [TestTable]

id    PD_ID doc_type    datetime

1     2901  A           2/1/2012
2     2901  B           2/2/2012
3     2901  C           2/3/2012
4     2902  A           2/4/2012
5     2902  A           2/5/2012
6     2903  B           2/6/2012
7     2903  B           2/6/2012
8     2904  C           2/7/2012
9     2905  A           2/8/2012
10    2905  A           2/9/2012
11    2905  B           2/10/2012
12    2905  B           2/10/2012
14    2905  C           2/11/2012

 
Script to Create Table
----------------------
SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[TestTable](

      [id] [int] IDENTITY(1,1) NOT NULL,

      [PD_ID] [int] NULL,

      [doc_type] [varchar](15) NULL,

      [date] [datetime] NOT NULL,

CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED

(

      [id] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

 

GO

SET ANSI_PADDING OFF
 

Script to Insert Data
----------------------
INSERT INTO TestTable (PD_ID, doc_type, date)
VALUES (2901, 'A', Convert(Datetime,'2/1/2012'))
INSERT INTO TestTable (PD_ID, doc_type, date)
VALUES (2901, 'B', Convert(Datetime,'2/2/2012'))
INSERT INTO TestTable (PD_ID, doc_type, date)
VALUES (2901, 'C', Convert(Datetime,'2/3/2012'))
INSERT INTO TestTable (PD_ID, doc_type, date)
VALUES (2902, 'A', Convert(Datetime,'2/4/2012'))
INSERT INTO TestTable (PD_ID, doc_type, date)
VALUES (2902, 'A', Convert(Datetime,'2/5/2012'))
INSERT INTO TestTable (PD_ID, doc_type, date)
VALUES (2903, 'B', Convert(Datetime,'2/6/2012'))
INSERT INTO TestTable (PD_ID, doc_type, date)
VALUES (2903, 'B', Convert(Datetime,'2/6/2012'))
INSERT INTO TestTable (PD_ID, doc_type, date)
VALUES (2904, 'C', Convert(Datetime,'2/7/2012'))
INSERT INTO TestTable (PD_ID, doc_type, date)
VALUES (2905, 'A', Convert(Datetime,'2/8/2012'))
INSERT INTO TestTable (PD_ID, doc_type, date)
VALUES (2905, 'A', Convert(Datetime,'2/9/2012'))
INSERT INTO TestTable (PD_ID, doc_type, date)
VALUES (2905, 'B', Convert(Datetime,'2/10/2012'))
INSERT INTO TestTable (PD_ID, doc_type, date)
VALUES (2905, 'B', Convert(Datetime,'2/10/2012'))
INSERT INTO TestTable (PD_ID, doc_type, date)
VALUES (2905, 'C', Convert(Datetime,'2/11/2012'))

 

Answer 1

Are you looking for something like this:

Declare @TestTable Table(
      [id] [int] IDENTITY(1,1) NOT NULL,
      [PD_ID] [int] NULL,
      [doc_type] [varchar](15) NULL,
      [date] [datetime] NOT NULL)

INSERT INTO @TestTable (PD_ID, doc_type, date)
VALUES (2901, 'A', Convert(Datetime,'2/1/2012'))
INSERT INTO @TestTable (PD_ID, doc_type, date)
VALUES (2901, 'B', Convert(Datetime,'2/2/2012'))
INSERT INTO @TestTable (PD_ID, doc_type, date)
VALUES (2901, 'C', Convert(Datetime,'2/3/2012'))
INSERT INTO @TestTable (PD_ID, doc_type, date)
VALUES (2902, 'A', Convert(Datetime,'2/4/2012'))
INSERT INTO @TestTable (PD_ID, doc_type, date)
VALUES (2902, 'A', Convert(Datetime,'2/5/2012'))
INSERT INTO @TestTable (PD_ID, doc_type, date)
VALUES (2903, 'B', Convert(Datetime,'2/6/2012'))
INSERT INTO @TestTable (PD_ID, doc_type, date)
VALUES (2903, 'B', Convert(Datetime,'2/6/2012'))
INSERT INTO @TestTable (PD_ID, doc_type, date)
VALUES (2904, 'C', Convert(Datetime,'2/7/2012'))
INSERT INTO @TestTable (PD_ID, doc_type, date)
VALUES (2905, 'A', Convert(Datetime,'2/8/2012'))
INSERT INTO @TestTable (PD_ID, doc_type, date)
VALUES (2905, 'A', Convert(Datetime,'2/9/2012'))
INSERT INTO @TestTable (PD_ID, doc_type, date)
VALUES (2905, 'B', Convert(Datetime,'2/10/2012'))
INSERT INTO @TestTable (PD_ID, doc_type, date)
VALUES (2905, 'B', Convert(Datetime,'2/10/2012'))
INSERT INTO @TestTable (PD_ID, doc_type, date)
VALUES (2905, 'C', Convert(Datetime,'2/11/2012'))
INSERT INTO @TestTable (PD_ID, doc_type, date)
VALUES (2906, 'C', Convert(Datetime,'2/11/2012'))


Select * From @TestTable As MainQry 
Where Exists (Select 1 From @TestTable As SubQry Where SubQry.doc_type In ('A', 'B', 'C') And SubQry.PD_ID = MainQry.PD_ID Group By SubQry.PD_ID Having Count(Distinct Doc_type) = 3)


--output
id	PD_ID	doc_type	date
1	2901	A	2012-02-01 00:00:00.000
2	2901	B	2012-02-02 00:00:00.000
3	2901	C	2012-02-03 00:00:00.000
9	2905	A	2012-02-08 00:00:00.000
10	2905	A	2012-02-09 00:00:00.000
11	2905	B	2012-02-10 00:00:00.000
12	2905	B	2012-02-10 00:00:00.000
13	2905	C	2012-02-11 00:00:00.000

---

Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

Answer 2

SELECT PD_ID FROM TestTable
WHERE Doc_Type = 'A'
INTERSECT
SELECT PD_ID FROM TestTable
WHERE Doc_Type = 'B'
INTERSECT
SELECT PD_ID FROM TestTable
WHERE Doc_Type = 'C'

This will give you the PD_ID, if you want to expand the columns you will have to add

SELECT * FROM TestTable WHERE PD_ID IN ({above code})

---

Best Wishes, The Redman; If something helps, please help show it by voting, if it solves, bonus!

Answer 3

Maybe something along these lines might help

WITH t1 AS(
        SELECT DISTINCT PD_ID, doc_type FROM TestTable
        ),
     t2 AS(
        SELECT PD_ID, Count(*) as valid_count FROM t1   GROUP BY PD_ID
        )
        

SELECT * FROM t2 WHERE valid_count =3 

Answer 4

Short answer: you want an exact relational division. This was one of Dr. Codd's original 8 operations and there are several ways to do it. I will get ot that 

Better answer: Your SQL needs to be cleaned up. CONVERT() is an old Sybase left over; we have CAST() now. But since we have a DATE data type, that code was a waste of time. And SQL use ISO-8601 date formats

There is no such thing as a generic magical “id” in RDBMS; that was physical record numbers in the old days. Bad SQL programmers will use IDENTITY to fake it. But it is not a key. 

You do not know ISO-11179 naming rules, etc. What you posted was a mag tape file written in bad SQL Here is a clean up; see the constraints? Proper key instead of an absurd IDENTITY? Formatted dates? 

CREATE TABLE Something_Tests
(test_id INTEGER NOT NULL,
 test_type CHAR(1) NOT NULL
 CHECK (doc_type IN ('A','B','C')),
 PRIMARY KEY (test_id, test_type, test_date),
 test_date DATE NOT NULL);

We have row constructor syntax now: 

INSERT INTO Something_Tests (test_id, test_type, test_date)
VALUES 
 (2901, 'A', '2012-02-01'), (2901, 'B', '2012-02-02'), 
 (2901, 'C', '2012-02-03'),
 (2902, 'A', '2012-02-04'), (2902, 'A', '2012-02-05'),
 (2903, 'B', '2012-02-06'), (2903, 'B', '2012-02-06'),
 (2904, 'C', '2012-02-07'),

 (2905, 'A', '2012-02-08'), (2905, 'A', '2012-02-09'),
 (2905, 'B', '2012-02-00'), (2905, 'B', '2012-02-10'),
 (2905, 'C', '2012-02-11'); 

You have a fixed divisor, the set {'A','B','C'}, so we can write it this way;

SELECT test_id
  FROM Something_Tests
 GROUP BY test_id
HAVING COUNT(*) = 3
   AND COUNT(DISTINCT test_type) = 3; 

You can Google more general forms of Relational Division that will work for division with remainders, and general divisors. 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 5

Wow! Thank you to GGoldspink, Arbi, jtclipper, and CELKO. All four answers were very valuable.
GGoldspink, I was able to most quickly apply your solution to my pre-existing situation and it resolved the problem.
Thank you to all.