asked on

Can some one re - write this sql into single select statement. It has to be compatible with sql server 2000 also

Hi ,

I have a query like this
Can 1 insert and 2 update statements can be written in single select statements (case statement may be )
create table #t_perm1
(
tab_name varchar(255) null,
parent_entity varchar(255) null,
child_entity varchar(255) null,
parent_tool_id varchar(255) null,
parent_tool_id2 varchar(255) null,
menubar_id int

)

insert into #t_perm1
select distinct 'Menus' ,c.name , a.name, c.tool_id , c.tool_id ,b.menubar_id
from country a, state b, country c
where a.tool_id = b.tool_id
and b.parent_tool_id = c.tool_id
and b.parent_tool_id <> ''
and b.form = 'main'
and b.toolbar = 'M'
and c.tool_id not in ('cal')

update #t_perm1
set parent_entity = c.name +'/' + parent_entity,
parent_tool_id2 = c.tool_id
--select c.name, a.*
from #t_perm1 a, state b, country c
where a.parent_tool_id = b.tool_id
and b.parent_tool_id = c.tool_id
and a.parent_tool_id <> ''
and b.form = 'main'
and b.toolbar = 'M'
and b.tool_id <> 'Msv'

update #t_perm1
set parent_entity = c.name +'/' + parent_entity
--select c.name, a.*
from #t_perm1 a, state b, country c
where a.parent_tool_id2 = b.tool_id
and b.parent_tool_id = c.tool_id
and a.parent_tool_id2 <> ''
and b.form = 'main'
and b.toolbar = 'M'
and b.tool_id <> 'Msv'

The above statement is working fine , but the requirement is to write in one Single statement.
can some one please help me

Kyle Abrahams, PMP

select distinct 'Menus' , case when b.tool_id is 'Msv' then c.name else c.name +'/' + parent_entity end as parent_entity, a.name, c.tool_id , c.tool_id ,b.menubar_id
from country a, state b, country c
where (a.tool_id = b.tool_id or a.parent_tool_id2 = b.tool_id)
and b.parent_tool_id = c.tool_id
and b.parent_tool_id <> ''
and b.form = 'main'
and b.toolbar = 'M'
and c.tool_id not in ('cal')

vijay11

ASKER

In one of the join column 'a.parent_tool_id2' is from temp table . i am getting the same error when I execute the sql

Thanks

vijay11

ASKER

and there are 2 update statements . So there can be parent\child\child relation

ASKER CERTIFIED SOLUTION

Lowfatspread

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

vijay11

ASKER

This was not the accurate answer but It helped me to re write the code my self