SQL multi-table join query instance

SQL multi-table join query instance

The following provides four SQL multi-table join query instances, with different efficiency.

Select * from order_info as a, ivrlog4ivrdlvinst as B

Where

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

And convert (varchar (10), a. instime, 112) = @ date2 and max (a. instime)

Method 2

Select * from order_info as

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 (10), max (a. instime), 112) = @ date2

Method 3

Declare

@ Date1 varchar (20 ),

@ Date2 varchar (20)

Set @ date1 = '000000'

Set @ date2 = '2017-08-12'

Select * from order_info as

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 (10), a. instime, 112) = @ date2

And max (a. instime)

Method 4

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 = '55' and B. ext1_skill! = '')

And convert (varchar (10), a. instime, 112) = @ date2

Order by B. start_date desc, B. start_time desc