Problem with Left Join in Cold Fusion

I have 2 tables that look like the following:

Table1: (Has unique channels)

Channel       Frequency
----------       --------------
2                    100
3                    106
4                    112
5                    118
...


Table2:

SystemID     Channel       Data
-----------       -----------     --------
1                      2               Data1
1                      3               Data2
2                      3               Data3
2                      4               Data4


My SQL statement is similar to:
SELECT [Table1].[Channel],[Table1].[Frequency],[Table2].[Data] FROM [Table1] LEFT JOIN [Table2] ON [Table1].[Channel]=[Table2].[Channel] WHERE [Table2].[SystemID]='1'

The output comes out like:

Channel        Frequency      Data
----------        -------------      -------
2                     100               Data1
3                     106               Data2


I want the output to list ALL the values from Table1 but with any values in Table2 with the SystemID specified put in place of the ones from Table1, for example:

Channel        Frequency      Data
----------        -------------      -------
2                     100               Data1
3                     106               Data2
4                     112
5                     118
...

This database is based in Microsoft Access and I am accessing the Data with Cold Fusion.  The resulting table is put into an HTML table format.

I am new to experts-exchange, so bear with me in the end with distribution of points. :)  Thank you for any help.
Belly23Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jyokumCommented:
SELECT table1.channel, table1.frequency, t2.data
FROM table1 LEFT JOIN (SELECT table2.data, table2.systemID FROM table2 WHERE table2.systemID = '1') t2 ON table1.channel=t2.channel
jyokumCommented:
oops, typo. ignore my original post

SELECT table1.channel, table1.frequency, t2.data
FROM table1 LEFT JOIN (SELECT table2.data, table2.channel FROM table2 WHERE table2.systemID = '1') t2 ON table1.channel=t2.channel

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Belly23Author Commented:
I tried it, with the <319 restriction and ordering, but it shouldnt cause an error, I am geting this error:

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

For this line:

<CFQUERY NAME="channels" DATASOURCE="myDatabase.mdb">SELECT Table1.Channel,Table2.Frequency, t2.data FROM Table1 LEFT JOIN (SELECT Table2.Data, Table2.Channel FROM Table2 WHERE Table2.SystemID = '1') t2 ON Table1.Channel=t2.Channel WHERE Table1.Frequency < 319 Order By Table1.Frequency</CFQUERY>

I tried it with the t2 in front of the other SQL statement and it didnt like that either with a syntax error.
jyokumCommented:
looks like Frequency is stored in Table1, you had Table2 in your select statement

SELECT Table1.Channel, Table1.Frequency, t2.data FROM Table1 LEFT JOIN (SELECT Table2.Data, Table2.Channel FROM Table2 WHERE Table2.SystemID = '1') t2 ON Table1.Channel=t2.Channel WHERE Table1.Frequency < 319 Order By Table1.Frequency
Belly23Author Commented:
I'm an idiot :)  My actual table names and fields have different names, and in converting them over I had t2.data instead of t2.ActualFieldName.   This now works when fixed, thank you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.