Query Help

[Jami]

Dear All

i have following DDL and sample data

[sql 2000]

create table main_tab (t_id varchar(10),fname varchar(30), lname
varchar(30), gender char(1))
create table im_tab (t_id varchar(10),flag char(1))

insert into main_tab values('a0101','Ali','Hussain','M')
insert into main_tab values('a0102','Noor','Inam','F')
insert into main_tab values('a0103','Siddique','Hussain','M')
insert into main_tab values('a0104','Farooq','Ali','M')
insert into main_tab values('a0105','Ahmed','Mushtaq','M')
insert into main_tab values('a0106','Qurban','Khan','M')
insert into main_tab values('a0107','Ahmed','Hasan','M')

insert into im_tab values('a0101','a')
insert into im_tab values('a0101','1')
insert into im_tab values('a0101','2')
insert into im_tab values('a0101','3')

insert into im_tab values('a0102','a')
insert into im_tab values('a0102','1')
insert into im_tab values('a0102','2')
insert into im_tab values('a0102','3')

insert into im_tab values('a0103','a')
insert into im_tab values('a0103','1')
insert into im_tab values('a0103','2')

insert into im_tab values('a0104','a')

insert into im_tab values('a0105','1')
insert into im_tab values('a0105','2')
insert into im_tab values('a0105','3')

i want to retrieve records from main_tab which have complete set of data
in im_table,
like a0101 and a0102 forms complete set in im_table

i also wanted another query which im not figuring out
is only those record which have flag 'a' like a0104

how can i do that

regards

*** Sent via Developersdex http://www.developersdex.com ***

[Tom Cooper]

One way to do the first query

Select m.t_id, m.fname, m.lname, m.gender
From main_tab m
Inner Join im_tab i On m.t_id = i.t_id
Group By m.t_id, m.fname, m.lname, m.gender
Having Count(*) = (Select Count(Distinct i2.flag) From im_tab i2);

And the second query

Select m.t_id, m.fname, m.lname, m.gender
From main_tab m
Inner Join im_tab i On m.t_id = i.t_id
Where i.flag = 'a' And
Not Exists(Select * From im_tab i2 Where m.t_id = i2.t_id And i2.flag <>
'a');

Tom

"Jami" <jami...@yahoo.com> wrote in message
news:uMFdFuI...@TK2MSFTNGP06.phx.gbl...