Left Outer Join for CF write CSV question

Things to check

1. The parenthesis in your FROM clause.  Try this: 

FROM ewc_table ewc

2. The WHERE clause.  sc.Level = active looks odd.  What is "active"? 

Message was edited by: JR "Bob" Dobbs

Thanks again for the input... (error message below)

Here's the scenerio I'm trying to do: This is for a school program teaching obesity lessons to students. At the beginning of a semester pre-heights and pre-weights and other stats are collected and at the end of the semester post-heights and post-weights and other stats are collected. A handful of schools will be participating. A CSV file will be uploaded that contains basic (State record) info on the student and, after collecting new data in different tables, a CSV will be available for downloading.

A school will upload a CSV file with State records such as student ID, School ID, Name, Gender, etc. and this upload builds a new table with this data. Partly for security in later queries, when this new table is built I'm assigning my own Student ID and School ID and other IDs to this table - autogenerated table Key + 1, table Key + 2, etc.

From this newly built table, the code will copy pertainent data fields to the other tables, with one 'main' table containing all the State IDs and all my own IDs. (So that later, when a school downloads a CSV with the data I can pull the State IDs, etc. that the school uses instead of my own personal IDs.) After the data in the CSV upload from the school is written to the other tables, the code will delete the newly uploaded table.

So... One 'main' table containing all IDs - State Student ID and 'my own' State Student ID, etc. This would be the table to base the query on, to pull data from the other tables. 'My own' Student and School IDs will be in each of the other tables... Some queries may address just one table, but for a  CSV download I may be addressing as many as 5 or more tables.

I hope I haven't made it too complicated. For logic, with all the different data to be collected, I need to make a handful of different tables. Each of the tables will be related to the one main table that contains all the IDs associated with one student and school.

Does this make sense in trying to do an outer join here, based on the one 'main' table that contains All the IDs? The one main table with IDs will contain some other pertainent data but it's mainly the other tables that I will need to pull all or most data from... based on the IDs in the one 'main' table.

And, as well, I do want to do the speediest thing with as little strain on the server. I certainly want to do the simplest most logical queries, which I know can begin with table layout in the database.

Just for other info here, login and other pages are dynamic PDFs from XML (Livecycle) through SSL, most pages running client side and admin edits confirmed with key certificate... queries parsed through Coldfusion.

Thanks...

Error message below that I get from the current code as it is in my original post.

- e

-----------------------

Error Executing Database Query. 
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. 
 
The error occurred in D:\ftp\website\Htdocs\m_folder\download\m_data.cfm: line 1

DATASOURCE   MATCH_data
VENDORERRORCODE   -3010
SQLSTATE   07002

-----------------------

Thanks for the advice.

The query isn't running at all (Error Executing Database Query).

It's sounding like I need to do a Right Outer Join as it's written right now.

"A" table contains all IDs and "B", "C" and "D" tables contain at least one ID that is in the "A" table... and I need to do a join that pulls most but not all of the fields from "B","C" and "D" tables based on the query to "A".

To pull fields from B,C,D tables based on an ID in A - I would need to query:

FROM B RIGHT OUTER JOIN A ON (A.aField = B.aField)
FROM C RIGHT OUTER JOIN A ON (A.aField = C.aField)
etc.

Maybe the logic would make sense then.

I'll experiment with this and post again here.

Thanks, guys.

- e

You need to do an outer join (left or right) whenever you have one table that is a set of values a

and another table that is a smaller, related set of values and you want all the values from the first table whether or not there is a matching record in the second.  Wheter this is a left or right outer join depends on what side of the equations the table with the desired records is in your SQL clause.  If you put it on the right, you need a right outer join if you put it on the left use a left outer join.

SQL syntax.

FROM allTable LEFT OUTER JOUN someTable ON (allTable.aField = someTable.aField)

OR

FROM someTable RIGHT OUTER JOIN allTabled ON (allTable.aField = someTable.aField).

Is the query running, or is it crashing?  If it's crashing, lose the brackets.

You do have a logic error.  When you do this:

select whatever

from t1 left join t2 on t1.somefield = t2.somefield

where t2.anyfield = something

the left join becomes an inner join.  One workaround is to do the left join to a subquery.  The other is to put "t2.anyfield = something" in the join portion of the from clause instead of the where clause.