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
191 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 39

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

759 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

21 Experts available now in Live!

Get 1:1 Help Now