SQL multi-table Joint Search example

SQL multi-table Joint Search example

Here are four examples of SQL multiple table associated queries, all of which are not as efficient.

SELECT * from Order_info as a, ivrlog4ivrdlvinst as B

where

(A.saleorder=b.ext1_skill and b.start_date= @date1 and se_id= ' and b.ext1_skill!= ')

and CONVERT (varchar, a.instime,112) = @date2 and Max (A.instime)

Method Two

SELECT * FROM Order_info as a

Where A.saleorder= (

Select B.ext1_skill from Ivrlog4ivrdlvinst as B

where b.start_date= @date1 and se_id= ' 55 '

and b.ext1_skill!= ')

and CONVERT (varchar), Max (A.instime), 112) = @date2

Method Three

Declare

@date1 varchar (20),

@date2 varchar (20)

Set @date1 = ' 20100812 '

Set @date2 = ' 2010-08-12 '

SELECT * FROM Order_info as a

where a.saleorder=

(select B.ext1_skill from ivrlog4ivrdlvinst as B where b.start_date= @date1 and se_id= ' and b.ext1_skill!= ')

and CONVERT (varchar, a.instime,112) = @date2

and Max (A.instime)

Method Four

Select B.caller, B.start_date, B.start_time, B.ext1_skill,

C.deliveryno, C.destroyresult, C.deliverydate, C.deliverytime, C.arrangetime, C.driverphone, C.drivermobile,

A.servicedate, A.servicetime, A.workertel

From Order_info as a, ivrlog4ivrdlvinst as B, Delivery_info as C

where

A.saleorder in (select B.ext1_skill from Ivrlog4ivrdlvinst where b.start_date= @date1 and b.se_id= ' ' and b.ext1_skill!= ' ')

and CONVERT (varchar, a.instime,112) = @date2

ORDER BY b.start_date Desc, b.start_time desc