3 table join

[weezerboy]

I have 3 related tables named

NAMEINFOF
FUNCTIONSF
RESTAURANTF

Each person has 1 row in each table

1 row in NAMEINFOF
1 row in FUNCTIONSF
1 row in RESTAURANTF


There is a column for each record in each table named conference_userid that relates the tables to each other.

For example PAT has a conference_userid of 66 and PAT has a row in each of the 3 tables.

I need to output the rows for Pat from each of the 3 tables in 1 query.

I get this error

[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.

[vksrinu]

Hi

Can you post your query here, so that we will see and help you.

quick help:
select n.* from NAMEINFOF n LEFT JOIN FUNCTIONSF f on n.conference_userid=f.conference_userid LEFT JOIN RESTAURANTF r on n.conference_userid=r.conference_userid where n.conference_userid=66

-Srinivas

[paross1]

Post your current code, then someone might be able to tell you why you are getting the error.

[weezerboy]

Here is the code..seems like this should work!!!
I have created relationships for the 3 tables on conference_userid

<cfquery name="conferencelist" datasource="conferences" dbtype="ODBC">
select n.* from NAMEINFOF n
LEFT JOIN FUNCTIONSF f on n.conference_userid=f.conference_userid
LEFT JOIN RESTAURANTF r on n.conference_userid=r.conference_userid where n.conference_userid=1
</cfquery>

Here is the error message

Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression &apos;n.conference_userid=f.conference_userid LEFT JOIN RESTAURANTF r on n.conference_userid=r.conference_userid&apos;.

[CF_Oracle]

You use dbtype that supposed to be for subqueries but you query tables so datasource would be enough.

[paross1]

Access is rather picky/demanding with the use of parantheses, especially with OUTER joins. It has been too long for me since writing such queries in Access for me to tell you exactly where to place your ( )'s, but you will need to group your clauses with them to avoid these errors.

This example is for nested INNER JOINs, but you should get the idea.....

In cases where you need to join more than one table, you can nest the INNER JOIN clauses. The following example builds on a previous SELECT statement to create the result set, but also includes the city and state of each customer by adding the INNER JOIN for the tblShipping table.

SELECT [Last Name], InvoiceDate, Amount, City, State
FROM (tblCustomers INNER JOIN tblInvoices
ON tblCustomers.CustomerID=tblInvoices.CustomerID)
INNER JOIN tblShipping
ON tblCustomers.CustomerID=tblShipping.CustomerID
ORDER BY InvoiceDate

Phil