need help on INNER JOIN

  • Thread starter Thread starter mark
  • Start date Start date
M

mark

Hi guys,

pls advise on as i'm having problem with the following inner join statement
involving 4 tables


SELECT * FROM tbl_CONFIRMED_INVOICE_DETAILS INNER JOIN
tbl_CONFIRMED_QUOTATION_DETAILS
INNER JOIN
tbl_SALES_PRODUCT_CAT INNER JOIN tbl_SALES_PRODUCT
ON tbl_SALES_PRODUCT.SaleProductCatID = tbl_SALES_PRODUCT_CAT.SalesCatID
ON tbl_CONFIRMED_INVOICE_DETAILS.InvoiceID='CQ/MW-A.W/10001/2004/VC'

It kept saying error on 'CQ/MW-A.W/10001/2004/VC' on the SQL server query
analyzer

-mark
 
Dear Mark:

I take it this is a query for SQL Server, not Jet, and you're using
Query Analyzer.

It looks like you intend the nesting to be parenthesized like this:

SELECT *
FROM tbl_CONFIRMED_INVOICE_DETAILS
(INNER JOIN tbl_CONFIRMED_QUOTATION_DETAILS
INNER JOIN tbl_SALES_PRODUCT_CAT
(INNER JOIN tbl_SALES_PRODUCT
ON tbl_SALES_PRODUCT.SaleProductCatID =
tbl_SALES_PRODUCT_CAT.SalesCatID)
ON tbl_CONFIRMED_INVOICE_DETAILS.InvoiceID =
'CQ/MW-A.W/10001/2004/VC')

I'd just as soon see this without parens:

SELECT *
FROM tbl_CONFIRMED_INVOICE_DETAILS CID
INNER JOIN tbl_CONFIRMED_QUOTATION_DETAILS CQD
ON CID.InvoiceID = 'CQ/MW-A.W/10001/2004/VC'
INNER JOIN tbl_SALES_PRODUCT_CAT SPC
INNER JOIN tbl_SALES_PRODUCT SP
ON SP.SaleProductCatID = SPC.SalesCatID

I have added some aliasing for readability.

Now, here are some problems:

- there must be one ON clause for each INNER JOIN.

- these ON clauses should relate each table JOINed so all are related

You have only two ON clauses for 3 inner joins, and one of them
doesn't relate 2 tables. It appears to be properly a WHERE clause,
not an ON clause. Perhaps it would look like this:

SELECT *
FROM tbl_CONFIRMED_INVOICE_DETAILS CID
INNER JOIN tbl_CONFIRMED_QUOTATION_DETAILS CQD
INNER JOIN tbl_SALES_PRODUCT_CAT SPC
INNER JOIN tbl_SALES_PRODUCT SP
ON SP.SaleProductCatID = SPC.SalesCatID
WHERE CID.InvoiceID = 'CQ/MW-A.W/10001/2004/VC'

You need 2 more ON clauses, showing how tables should relate, or you
need to not use INNER JOINs. Maybe like this:

SELECT *
FROM tbl_CONFIRMED_INVOICE_DETAILS CID
INNER JOIN tbl_CONFIRMED_QUOTATION_DETAILS CQD
ON CQD.???? = CID.????
INNER JOIN tbl_SALES_PRODUCT_CAT SPC
ON SPC.???? = {CID or CQD}.????
INNER JOIN tbl_SALES_PRODUCT SP
ON SP.SaleProductCatID = SPC.SalesCatID
WHERE CID.InvoiceID = 'CQ/MW-A.W/10001/2004/VC'

In the first of these, show how CQD (my alias for
tbl_CONFIRMED_QUOTATION_DETAILS) relates to CID
(tbl_CONFIRMED_INVOICE_DETAILS). In the second, SPC
(tbl_SALES_PRODUCT_CAT) should probably relate to CID or CQD.

You might consider using Hungarian notation in future, such as:

tblConfirmedInvoiceDetails
tblConfirmedQuotationDetails
tblSalesProductCat
tblSalesProduct

Just a minor detail.

Did this help at all?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Hi,

You are missing at least one ON clause.


When you make an INNER JOIN, you imply two tables in one condition.
Actually, you have


FROM a INNER JOIN
b INNER JOIN
c INNER JOIN d ON c.f1=d.g1
ON b.h1 = constant


There is no link between b and either c either d,
no link between a and any of the other tables.

The clause ON b.h1=constant, here, could be placed in a WHERE clause, since
it does not imply two tables.


FROM a INNER JOIN
b INNER JOIN
c INNER JOIN d ON c.f1=d.g1
ON b.h1 = d.z1
ON a.k1=d.w1


as example, or, with parentheses:

FROM
a INNER JOIN
( b INNER JOIN
( c INNER JOIN d ON c.f1=d.g1 )
ON b.h1 = d.z1
)
ON a.k1=d.w1



where each join is delimited by ( ).



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top