join expressions? multiple conditions. 1

[imstillatwork]

in other dbs, this works just fine:

SELECT fields...
FROM table1 AS t1
LEFT JOIN table2 AS t2
ON t2.table1_id = t2.id AND t2.type = 3

I get join expression not supported errors.

I need to provide more then one condition for the join, I generaly use mySQL, but this project is stuck to access, which is fine by me, it's just been a while.

thank!!




Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!

 

[BSman]

Try changing your SQL statement to this:

SELECT fields...
FROM table1 AS t1
LEFT JOIN table2 AS t2
ON t1.id = t2.id AND t2.type = 3


Bob

 

[Golom]

Your join condition doesn't mention any fields from Table1.

SELECT fields...
FROM table1 AS t1
LEFT JOIN table2 AS t2
ON [COLOR=red]t2[/color].table1_id = [COLOR=red]t2[/color].id AND [COLOR=red]t2[/color].type = 3

 

[PHV]

A starting point in JetSQL compatible syntax:
SELECT fields...
FROM table1 AS t1 LEFT JOIN (
SELECT fields... FROM table2 WHERE type = 3
) AS t2 ON t1.SomeID = t2.AnotherID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[imstillatwork]

there was indeed a typo in my question

SELECT fields...
FROM table1 AS t1
LEFT JOIN table2 AS t2
ON t2.table1_id = t1.id AND t2.type = 3

I'll check the res of your posts also, thanks!


Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!

 

[IT4EVR]

HOw about this one?

SELECT fields...
FROM table1 AS t1
LEFT JOIN table2 AS t2
ON t2.table1_id = t1.id
WHERE t2.type = 3

 

[PHV]

WHERE t2.type = 3
Doing that defeats the OUTER join purpose ...
With Kevin correction:
SELECT fields...
FROM table1 AS t1 LEFT JOIN (
SELECT fields... FROM table2 WHERE type = 3
) AS t2 ON t1.id = t2.table1_id

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[IT4EVR]

PHV, you are right, thanks for the correction.

SELECT fields... FROM table2 WHERE type = 3 is considered a derived table correct?

 

[imstillatwork]

yes, the join table needs the condition, not the final row. otherwise a table1 row with no matching table 2 contisions will not return a row, but I need all the table one rows no matter what.

thanks PHV!

Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!

 

[PHV]

is considered a derived table
I personally say an inline view