Solved

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

Posted on 2011-02-14
5
192 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:vijay11
  • 3
5 Comments
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 34890265
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')

0
 

Author Comment

by:vijay11
ID: 34890424
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
0
 

Author Comment

by:vijay11
ID: 34890444
and there are 2 update statements . So there can be parent\child\child relation
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 34891478
try this....

need to left join  the initial  Insert query with the other two updates
and insert (select) into the temp table...


you need to check both the result and format of the columns involved
and convert/cast as necessary....

you could also put this / the original into a stored procedure and "just" execute 1 statement...
(with a global temp table perhaps?)
Select A.Tab_name
      ,coalesce(C.Name+'/','')+coalesce(B.Name + '/','')+ A.Parent_entity as Parent_entity
      ,A.Child_entity
      ,A.parent_tool_id
      ,coalesce(B.tool_id,A.parent_tool_id2) as Parent_tool_id2
      ,A.Menubar
  Into #t_perm1
  From (
select distinct 'Menus' as Tab_name
      ,c.name as parent_entity
      , a.name as child_entity
      , c.tool_id as parent_tool_id
      , c.tool_id as parent_tool_id2
      ,b.menubar_id  as menubar_id
  from country a
 Inner Join (select * 
               from state
               Where parent_tool_id <> '' 
                 and form    = 'main' 
                 and toolbar = 'M'
            ) as b
    on a.tool_id = b.tool_id 
 Inner Join (Select * 
               from country
              where tool_id  not in ('cal')
            ) as c
    on b.parent_tool_id = c.tool_id
       ) as A

 Left Outer Join 
           (select s.tool_id as Btoolid,c.tool_id,C.Name
               from state as S
              Inner Join country c
                on S.parent_tool_id  = c.tool_id  
              where S.form           = 'main' 
                and S.toolbar        = 'M'  
                and S.tool_id        <> 'Msv'
                and S.Tool_id <> ''
            ) as B
    on A.parent_tool_id = B.btool_id 
 
 Left Outer Join
       (select b.tool_id as Btoolid,c.name
          from state b
         Inner Join country c
            on b.parent_tool_id  = c.tool_id
         Where b.form           = 'main' 
           and b.toolbar        = 'M'  
           and b.tool_id        <> 'Msv'
           and b.tool_id <> ''
       ) as C
ON  a.parent_tool_id2 = C.btool_id

Open in new window

0
 

Author Closing Comment

by:vijay11
ID: 35012927
This was not the accurate answer but It helped me to re write the code my self
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now