February 13, 2002 at 8:18 am
Hi, All
I have a q for U 🙂
First of all, here is table and data..
/*************************************
create table [datamart].[dbo].[test]
(
ID int NOT NULL,
EFFDTdatetime NOT NULL,
FTchar(2) NOT NULL
)
insert into [datamart].[dbo].[test] values(1,'1/1/00','F')
insert into [datamart].[dbo].[test] values(1,'2/1/00','F')
insert into [datamart].[dbo].[test] values(2,'2/1/00','P')
insert into [datamart].[dbo].[test] values(2,'3/1/00','P')
insert into [datamart].[dbo].[test] values(3,'1/1/00','P')
insert into [datamart].[dbo].[test] values(3,'1/30/00','P')
insert into [datamart].[dbo].[test] values(3,'2/1/00','F')
/*****************************************
Q is that how can I pull ID that has P and F(ID 3)..??
I dont want to pull ID that has P or F only..
Thx In advance..
Jay
February 13, 2002 at 8:32 am
select ID from (select distinct ID, FT from Test where FT='F' or FT='P') Test2
group by ID having count(ID)>1
Jay Madren
February 13, 2002 at 9:31 am
Thx..
That works great..:-))
One more thing.. What if I want to pull ID and EFFDT and FT all not just ID? Actually I wanted pull all columns not just ID.. My Q was not specific.. Sorry about that..
I managed pull all columns but for somebody who reads this..:-)))
Jay
February 13, 2002 at 9:47 am
If you want to pull other columns, then you will be getting more than one row for each ID. Is this what you want? In this example, ID 3 met the criteria of having both "F" and "P", so do you want the date (and any other columns you might have) for both the "F" and "P" row? And might there be more than one "F" or "P" row for ID 3? If yes, then that would increase the number of rows returned for that ID. Then, all of this will be repeated for each ID that meets the criteria, and the results will be lumped together into one list, i.e., 2 rows for ID 3 followed by 3 rows for ID 22 followed by 5 rows for ID 57, etc. (I just made these up for this illustration).
Jay Madren
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply