Query Help required - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query Help required

Author

Topic

gfaryd
Starting Member

27 Posts

Posted - 2010-08-25 : 18:44:38

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 wanted a query which im not figuring out
is only those record which forms incomplete or no set in im_table with respect to main_tab
like
a0103
a0104
a0105
a0106
a0107

how can i do that

regards

Devart
Posting Yak Master

102 Posts

Posted - 2010-08-26 : 02:32:06

Hello,

If you want to select what exists in main_tab, but does not exist in im_tab, try to do the following:

SELECT
main_tab.*
FROM main_tab
LEFT OUTER JOIN im_tab
ON main_tab.t_id=im_tab.t_id
WHERE
im_tab.t_id IS NULL

Best regards,

Devart,
SQL Server Tools:
dbForge Schema Compare
dbForge Data Compare
dbForge Query Builder

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-26 : 11:16:54

[code]
SELECT m.t_id
FROM
(
SELECT t_id,flag
FROM main_tab
CROSS JOIN (SELECT DISTINCT flag FROM im_tab)i
)m
LEFT JOIN im_tab n
ON n.t_id= m. t_id
AND n.flag = m.flag
GROUP BY m.t_id
HAVING COUNT(m.flag) > COUNT(n.flag)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources