join on condition, problem, even possible?

[lajkonik86]

I have a little problem:

the sql:
SELECT *
FROM confc c
LEFT JOIN template t ON ( c . cname = 'template' AND c . cvalue = t . id )
WHERE ( c . foreignid = 1 AND foreignref = 1 ) OR ( c . foreignid = 2 AND foreignref = 2 )

the test data:
foreignid foreignref cname cvalue
1 1 template 1
1 2 template 2
1 3 template 17
1 1 maxlevels 17
2 2 template 1

the problem:
this doesn't select the maxlevels row. I want the c.cname = 'template' condition to be just used for the join, not for the rest of the query.
Is this even possible?

[nemonoman]

Never mind. I have no idea what results you are trying to get. If you want the maxlevel row, why are saying you don't want the row (c . cname = 'template' ??)

[lajkonik86]

i want the max row, but i only want to join if the column value = 'template'

never mind, it was a strange db design. I saw it in phpbb like this once, but its not really a good idea.

Changed it to something more normal now.