About
Join syntax for two joins is shown below. The first shows the default behavior where the join is defined in the WHERE clause, the second shows the same join in the FROM clause using the ANSI 92 standard.
Articles Related
Examples
Default join syntax
SELECT
Resort.resort,
'FY'+Format(Sales.invoice_date,'YYYY'),
sum(Invoice_Line.days * Invoice_Line.nb_guests * Ser
vice.price)
FROM
Resort,
Sales,
Invoice_Line,
Service,
Service_Line
WHERE
( Sales.inv_id=Invoice_Line.inv_id )
AND ( Invoice_Line.service_id=Service.service_id )
AND ( Resort.resort_id=Service_Line.resort_id )
AND ( Service.sl_id=Service_Line.sl_id )
GROUP BY
Resort.resort,
'FY'+Format(Sales.invoice_date,'YYYY')
Same join using the ANSI 92 standard
SELECT
Resort.resort,
'FY'+Format(Sales.invoice_date,'YYYY'),
sum(Invoice_Line.days * Invoice_Line.nb_guests * Ser
vice.price)
FROM
Resort INNER JOIN Service_Line ON (Resort.resort_id=Ser
vice_Line.resort_id)
INNER JOIN Service ON (Service.sl_id=Service_Line.sl_id)
INNER JOIN Invoice_Line ON (Invoice_Line.service_id=Ser
vice.service_id)
INNER JOIN Sales ON (Sales.inv_id=Invoice_Line.inv_id)
GROUP BY
Resort.resort,
'FY'+Format(Sales.invoice_date,'YYYY')