4 table JOIN problem

[TimMcGeary]

I am struggling to come up with the proper JOIN syntax for this SQL query. It
is for an Access Database. I am receiving a column name (#person_type) and
value (#person_value) from a form. I need the requestor_id from the requestor
table, which I can use to pull up all of the requests from table requests. But
I also need the status of each request which is mapped in the status table,
with status.status_id related to the status_def table giving me the description
for the status_id.

The error I am getting missing operator. I've tried so many combinations of
LEFT, OUTER, and INNER JOINS that I don't know if I am doing myself any good
any more. HELP!

:confused;

SELECT r.request_id, r.title, r.author, r.callnum, r.article_title, r.barcode,
sd.status_id, sd.definition, s.status_id, rby.requestor_id
(FROM requestors rby LEFT JOIN requests r ON rby.requestor_id = r.requestor_id)
LEFT JOIN status s ON r.request_id = s.request_id
INNER JOIN status_def sd ON s.status_id = sd.status_id
WHERE rby.#person_type# = '#person_value#'

[jorgepino]

did you tried without the parenthesis around the (From ....)

[mxstu]

Passing URL parameters used directly in a cfquery is risky, so you should read
up on sql injection risks.

Access requires a lot of ugly parenethesis for JOINS. I guess that is how it
establishes JOIN precedence and resolves ambiguity. Are LEFT JOINS required
here, or were you just trying to get the query to work? If you only want to
return records where all of the tables contain matching records, then use INNER
JOINS. This should be the right syntax for Access

SELECT r.request_id, r.title, r.author, r.callnum, r.article_title, r.barcode,
sd.status_id, sd.definition, s.status_id, rby.requestor_id
FROM ( (requestors rby INNER JOIN requests r ON rby.requestor_id =
r.requestor_id)
INNER JOIN status s ON r.request_id = s.request_id)
INNER JOIN status_def sd ON s.status_id = sd.status_id
WHERE rby.#person_type# = '#person_value#'

[jorgepino]

correct but you had
( FROM requestors rby LEFT JOIN requests r ON rby.requestor_id = r.requestor_id)
instead of
FROM (requestors rby LEFT JOIN requests r ON rby.requestor_id = r.requestor_id)

[TimMcGeary]

mxstu, I had actually tried this... almost. I didn't have the ( )'s properly
set. Thank you for your editing of it. It works.

I have never heard of any risks of passing URL parameters into cfqueries. In
fact, the ColdFusion training I had advocated it in the examples.

Can you point me to documentation of the risks? I would definitely like to
read up on it.

Also - can you briefly state some of the risks or what I should do instead?

Thanks!

[jorgepino]

if you realy want to be safe you should use <cfqueryparam>

Select LastName
form tablelist
WHERE LastName= <cfqueryparam value="#LastName#" cfsqltype="CF_SQL_VARCHAR" maxlength="17">

[TimMcGeary]

Excellent. Thank you that.

[mxstu]

TimMcGeary,

Generally speaking, sql injection is when a user attempts to pass sql commands
through form or url variables, in an attempt to make your sql query perform in
a way that you did not intend. For example, passing an extra statement that
would cause your query to delete information from other tables or cause a login
query to successfully authenticate an invalid user. Any time you accept input
from users that will be used in a SQL query (ex. url or form variables), you
need to guard against sql injection. Macromedia recommends using the
CFQUEYRPARAM tag to protect against sql injection.

IMO, a good rule is to never blindly accept URL and FORM variables.


[url]http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/tags-b20.htm[/url]