How to do a join between 2 tables on 3 columns? Can a join use an "AND" ?

CoolCode

Hello,

I am trying to do a simple inner join between 2 tables
on 3 columns as in this query below:

SELECT
B.b,
B.cn,
B.v,
A.topic
FROM topic A
INNER JOIN story B
ON A.bn = B.bn
AND A.cn = B.cn
AND A.vn = B.vn
WHERE A.topic = 'river'

When I do the query using the Editor, there are duplicate results.

Is there not a way to do a join with an "AND" rather than
do a join 3 times with an alias as shown below?

Editor::inst( $db, 'story' )
->field(
Field::inst( 'story.b' ),
Field::inst( 'story.cn' ),
Field::inst( 'story.vn' ),
Field::inst( 'story.line' )
)
->leftJoin( 'topic','topic.bn','=', 'story.bn')
->leftJoin( 'topic as topi','topi.cn','=', 'story.cn')
->leftJoin( 'topic as top','top.vn','=', 'story.vn')
->where( 'topic.topic', 'river' )
->process($_POST)
->json();

How do I do a join between 2 tables on 3 columns?

I am doing a leftJoin because the link only allows 2 joins
and I need to do a join on 3 columns between 2 tables.

thank you,
Jeff

allan

Hi Jeff,

Yes it is possible in the PHP libraries, but its a little bit of a hack. This thread discusses how it can be done.

Regards,
Allan