Sql 2000 Query help - 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

Sql 2000 Query help

Author

Topic

gfaryd
Starting Member

27 Posts

Posted - 2010-09-13 : 15:48:44

Dear all

i have following DDL and sample data i m using sql 2000

CREATE TABLE [data_table] (
[m_id] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[group_id] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[model_value] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_data_table] PRIMARY KEY CLUSTERED
(
[m_id],
[group_id]
) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [work_table] (
[m_id] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[group_id] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[data_value] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
) ON [PRIMARY]
GO

insert into data_table values('A001','1','AB900')
insert into data_table values('A001','2','AB100')
insert into data_table values('A001','3','AB2000')
insert into data_table values('A001','4','AB700')

insert into data_table values('A002','1','CF1000')
insert into data_table values('A002','3','CF4000')
insert into data_table values('A002','4','CF9000')

insert into data_table values('A003','3','DE4000')
insert into data_table values('A003','4','DE9000')

insert into data_table values('A004','2','GT4000')
insert into data_table values('A004','3','GT9000')

insert into work_table values('A001','1','AB900')
insert into work_table values('A001','2','AB100')
insert into work_table values('A001','3','AB2000')
insert into work_table values('A001','4','AB700')

insert into work_table values('A002','3','CF4000')
insert into work_table values('A002','4','CF9000')

insert into work_table values('A003','2','DE4000')
insert into work_table values('A003','3','DE9000')

insert into work_table values('A004','3','GT4000')
insert into work_table values('A004','4','GT9000')

i m running following query

select a.*,b.* from data_table a left join work_table b
on a.m_id = b.m_id and a.group_id = b.group_id
where a.group_id = 1 and b.m_id is not null
union
select a.*,b.* from data_table a left join work_table b
on a.m_id = b.m_id and a.group_id = b.group_id
where a.group_id = 2 and b.m_id is not null
union
select a.*,b.* from data_table a left join work_table b
on a.m_id = b.m_id and a.group_id = b.group_id
where a.group_id = 3 and b.m_id is not null
union
select a.*,b.* from data_table a left join work_table b
on a.m_id = b.m_id and a.group_id = b.group_id
where a.group_id = 4 and b.m_id is not null

the query is giving me result

A001 1 AB900 A001 1 AB900
A001 2 AB100 A001 2 AB100
A001 3 AB2000 A001 3 AB2000
A001 4 AB700 A001 4 AB700
A002 3 CF4000 A002 3 CF4000
A002 4 CF9000 A002 4 CF9000
A003 3 DE4000 A003 3 DE9000
A004 3 GT9000 A004 3 GT4000

but i want out put like

A001 1 AB900 A001 1 AB900
A002 3 CF4000 A002 3 CF4000
A003 3 DE4000 A003 3 DE9000
A004 3 GT9000 A004 3 GT4000

i only want one group id in a m_id like in original resultset if group_id 1 is avaialable for m_id A001 in both tables then group_id's 2,3,4 should not be returned in result set and same applies for all data.

Regards,

Devart
Posting Yak Master

102 Posts

Posted - 2010-09-14 : 02:42:55

Hello,
For example:

create view [dbo].[data_work_view]
as
select a.m_id,a.group_id,a.model_value,b.data_value from data_table a left join work_table b
on a.m_id = b.m_id and a.group_id = b.group_id
where a.group_id = 1 and b.m_id is not null
union
select a.m_id,a.group_id,a.model_value,b.data_value from data_table a left join work_table b
on a.m_id = b.m_id and a.group_id = b.group_id
where a.group_id = 2 and b.m_id is not null
union
select a.m_id,a.group_id,a.model_value,b.data_value from data_table a left join work_table b
on a.m_id = b.m_id and a.group_id = b.group_id
where a.group_id = 3 and b.m_id is not null
union
select a.m_id,a.group_id,a.model_value,b.data_value from data_table a left join work_table b
on a.m_id = b.m_id and a.group_id = b.group_id
where a.group_id = 4 and b.m_id is not null

GO

select
m_id,
group_id,
model_value,
data_value
from
dbo.data_work_view dwv
where
(select count(*) from data_work_view where m_id=dwv.m_id and group_id<=dwv.group_id)=1

Best regards,

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

PavanKK
Starting Member

32 Posts

Posted - 2010-09-14 : 03:11:46

hi gfaryd,

try the following query

select a.*,b.*
from data_table a
join work_table b on a.m_id = b.m_id and a.group_id = b.group_id
WHERE a.group_id =
(
SELECT top 1 c.group_id
FROM work_table c
JOIN data_table d on c.m_id = d.m_id and c.group_id = d.group_id and c.m_id = a.m_id
order by c.group_id
)

KK

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-16 : 12:55:59

[code]
select * into #Temp
from
(
select a.*,b.* from data_table a left join work_table b
on a.m_id = b.m_id and a.group_id = b.group_id
where a.group_id = 1 and b.m_id is not null
union
select a.*,b.* from data_table a left join work_table b
on a.m_id = b.m_id and a.group_id = b.group_id
where a.group_id = 2 and b.m_id is not null
union
select a.*,b.* from data_table a left join work_table b
on a.m_id = b.m_id and a.group_id = b.group_id
where a.group_id = 3 and b.m_id is not null
union
select a.*,b.* from data_table a left join work_table b
on a.m_id = b.m_id and a.group_id = b.group_id
where a.group_id = 4 and b.m_id is not null
)t

select t1.*
from #Temp t1
inner join (select m_id,MIN(group_id) AS MinGroup
from #Temp
group by m_id) t2
on t2.m_id = t1.m_id
and t2.MinGroup = t1.group_id
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources