SQL join on multiple columns in same tables

Question

I have 2 subqueries, but I'm having trouble joining columns together from the same tables. I tried:

SELECT * FROM

(SELECT userid, listid 
FROM user_views_table
WHERE date='2013-05-15' AND view_type='lists') a

JOIN

(SELECT sourceid, destinationid
FROM actions_table
WHERE date='2013-05-15' AND payloadtype='lists_user' AND actiontype='delete') b

ON a.userid = b.sourceid
ON a.listid = b.destinationid;

If I simply end the query with ON a.userid = b.sourceid it works, but how can I also join these tables on another column also ON a.listid = b.destinationid ??

Any help appreciated.

Answer 1

You need to replace the second ON with AND, like this:

ON a.userid = b.sourceid AND a.listid = b.destinationid;

Answer 2

You want to join on condition 1 AND condition 2, so simply use the AND keyword as below

ON a.userid = b.sourceid AND a.listid = b.destinationid;