COMPUTER LEARNING CENTER
WWW.HUUKHANG.COM
Select c.CustName,
s.OrderID,s.OrderDate,
s.TotalAmount
from tblCustomer c
inner join tblOrders s
On c.CustID=s.CustID
where month (s.OrderDate) = 10
order by c.CustName DESC
Keát quaû traû veà nhö sau:
CustName
OrderID OrderDate .. TotalAmount
----------------------------------------------CENTURY Hotel 13 2001-
10-17 ..
388800000
CENTURY Hotel 14 2001-10-18 ..
518400000
CENTURY Hotel 16 2001-10-17 ..
388800000
CENTURY Hotel 17 2001-10-18 ..
14400000
CENTURY Hotel 18 2001-10-18 ..
12960000
CENTURY Hotel 11
2001-10-18 ..
216000000
Plaza Hotel
12 2001-10-17 ..
403200000
Plaza Hotel
19 2001-10-17 ..
86400000
Plaza Hotel 11 2001-10-17 ..
576000000
Plaza Hotel 15 2001-10-17 ..
288000000
Taát nhieân, baïn cuõng coù theå vieát phaùt bieåu treân öùng vôùi töøng coät muoán laáy ra baèng caùch khai baùo
teân coät.
6.4.
Meänh ñeà Left Join
Tröôøng hôïp baïn mong muoán keát quaû laáy ra trong hai baûng keát hôïp nhau theo ñieàu kieän: Nhöõng
maåu tin baûng beân traùi toàn taïi öùng vôùi nhöõng maåu tin ôû baûng beân phaûi khoâng toàn taïi baïn haõy duøng
meänh ñeà LEFT JOIN trong phaùt bieåu SQL daïng SELECT, cuù phaùp coù daïng:
select <Column list>
from lefttablename
LEFT JOIN righttablename
on lefttabkename.field1=righttablename.field2
Where <conditions>
Order by <column name>
ASC/DESC
Chaúng haïn, baïn choïn ra taát caû caùc saûn phaåm (vôùi caùc coät) coù hay khoâng coù doanh soá baùn trong
thaùng hieän taïi. Moät soá saûn phaåm khoâng baùn trong thaùng seõ coù coät Amount coù coät Amount giaù trò
NULL.
Ví duï 8-26: SELECT duøng LEFT JOIN
/* in ra danh saùch saûn phaåm baùn trong thaùng 10 */
select ItemID,ItemName,Amount
from tblItems
left join tblOrderDetails
on tblItems.ItemID=tblOrderDetails.ItemID
order by Amount
Keát quaû traû veà nhö sau:
ItemID ItemName
Amount
----------------------------------------------
12
ASW-60VP
NULL
Giaùo vieân: Phaïm Höõu Khang
