We help IT Professionals succeed at work.

how to rewrite sql query

James Murrell
James Murrell used Ask the Experts™
on
I have below query but in moving to sql 2008 this code will not run, and i a ashamed to admit i don't understand

the main bit in a join (unsure)

 and sa.mystaff_id *= sb.mystaff_id
select sa.mystaff_id,ad.avail_date,sa.time_from,sa.lenght, vs.fname,vs.sname,vs.mobile,vl.tel1,sb.datetime_id,
 (select time_from from order_reqt where reqt_id = sb.reqt_id) as datetime_time_from,
 (select lenght from order_reqt where reqt_id = sb.reqt_id) as datetime_lenght,
 (select description from order_reqt r,product p where r.reqt_id = sb.reqt_id and r.product_id = p.product_id) as datetime_product,
 (select (vc.fname + ' ' + vc.sname) from order_reqt r,main_order mo,vclient vc where r.reqt_id = sb.reqt_id and r.order_id = mo.order_id and mo.client_id = vc.client_id) as client_name
 from mystaff_avail sa, avail_dates ad, vmystaff vs, mystaff_booking sb, vaddress vl
 where ad.avail_id = sa.avail_id
 and vs.mystaff_id = sa.mystaff_id
 and vl.address_id = vs.address_id
 and sa.mystaff_id *= sb.mystaff_id
 and sb.datetime_id in (select datetime_id from datetime_plan where datetime_from between ad.datetime_from and ad.datetime_to)
and ad.avail_date_id not in 
(select ad1.avail_date_id from avail_dates ad1, mystaff_break sb where 
sb.mystaff_id = sa.mystaff_id
and ad1.avail_date_id = ad.avail_date_id
and dateadd(mi,sa.time_from,ad1.avail_date) < sb.datetime_to
 and dateadd(mi,sa.time_from+sa.lenght,ad1.avail_date) >= sb.datetime_from)

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Consultant
Top Expert 2009
Commented:
select sa.mystaff_id,ad.avail_date,sa.time_from,sa.lenght, vs.fname,vs.sname,vs.mobile,vl.tel1,sb.datetime_id,
 (select time_from from order_reqt where reqt_id = sb.reqt_id) as datetime_time_from,
 (select lenght from order_reqt where reqt_id = sb.reqt_id) as datetime_lenght,
 (select description from order_reqt r,product p where r.reqt_id = sb.reqt_id and r.product_id = p.product_id) as datetime_product,
 (select (vc.fname + ' ' + vc.sname)
 from order_reqt r,main_order mo,vclient vc where r.reqt_id = sb.reqt_id and r.order_id = mo.order_id and mo.client_id = vc.client_id) as client_name
 from mystaff_avail sa, avail_dates ad, vmystaff vs, vaddress vl
 LEFT JOIN mystaff_booking sb ON sa.mystaff_id = sb.mystaff_id
 where ad.avail_id = sa.avail_id
 and vs.mystaff_id = sa.mystaff_id
 and vl.address_id = vs.address_id
 and sb.datetime_id in (select datetime_id from datetime_plan where datetime_from between ad.datetime_from and ad.datetime_to)
and ad.avail_date_id not in
(select ad1.avail_date_id from avail_dates ad1, mystaff_break sb where
sb.mystaff_id = sa.mystaff_id
and ad1.avail_date_id = ad.avail_date_id
and dateadd(mi,sa.time_from,ad1.avail_date) < sb.datetime_to
 and dateadd(mi,sa.time_from+sa.lenght,ad1.avail_date) >= sb.datetime_from)

Chris LuttrellSenior Database Architect
Commented:
your *= is old syntax for left outer join you need to change your joins to use the ansi standard like below

try this statement:
select sa.mystaff_id,ad.avail_date,sa.time_from,sa.lenght, vs.fname,vs.sname,vs.mobile,vl.tel1,sb.datetime_id, 
 (select time_from from order_reqt where reqt_id = sb.reqt_id) as datetime_time_from, 
 (select lenght from order_reqt where reqt_id = sb.reqt_id) as datetime_lenght, 
 (select description from order_reqt r,product p where r.reqt_id = sb.reqt_id and r.product_id = p.product_id) as datetime_product, 
 (select (vc.fname + ' ' + vc.sname) from order_reqt r,main_order mo,vclient vc where r.reqt_id = sb.reqt_id and r.order_id = mo.order_id and mo.client_id = vc.client_id) as client_name 
from    mystaff_avail sa 
        JOIN avail_dates ad ON ad.avail_id = sa.avail_id
        JOIN vmystaff vs ON vs.mystaff_id = sa.mystaff_id
        LEFT OUTER JOIN mystaff_booking sb on sa.mystaff_id = sb.mystaff_id
        and sb.datetime_id in ( select  datetime_id
                                from    datetime_plan
                                where   datetime_from between ad.datetime_from
                                                      and     ad.datetime_to )
        JOIN vaddress vl ON vl.address_id = vs.address_id
where   ad.avail_date_id not in (
select ad1.avail_date_id from avail_dates ad1, mystaff_break sb where  
sb.mystaff_id = sa.mystaff_id 
and ad1.avail_date_id = ad.avail_date_id 
and dateadd(mi,sa.time_from,ad1.avail_date) < sb.datetime_to 
 and dateadd(mi,sa.time_from+sa.lenght,ad1.avail_date) >= sb.datetime_from)

Open in new window

James MurrellProduct Specialist

Author

Commented:
Ah that explains it =  *= is old syntax for left outer join, will check back v soon....
James MurrellProduct Specialist

Author

Commented:
Thanks only 20 other scripts to go