Problem with JOIN on multiple columns

Hi everyone,

I'm having a lot problems trying to join tables. I hope some on smart can help me.

I want to join two tables that have 2 columns on both tables to uniquely identify a record. Right now I'm using this command for the join.

SELECT DISTINCT files.*
FROM files
JOIN transactions n1 ON n1.product_id = files.product_id
JOIN transactions n2 ON n2.script_id = files.script_id
WHERE transactions.order_number = '123456';

Code (markup):

It's still returning rows in the files table where the script_id doesn't my match product_id and script_id in the transactions table.

For example:
The product_id is 10 and script_id is 20 for order number 123456.

It will return two records 2 records.
Record 1:
product_id = 10
script_id = 20
Record 2
product_id = 10
script_id = 21

The product_id will match but script_id doesn't match. I think it's the way I have the join in my command.

I tried left, right, inner joins and joining using the where clause but nothing seem to help. I tried kicking my computer and that didn't help either.

I'm sure this is something simple but it's driving me crazy.

 

Try this:

SELECT f.* FROM transactions t INNER 
JOIN files f ON t.product_id = f.product_id AND t.script_id = f.script_id
WHERE t.order_number = 123456;

Code (markup):

You may also experiment by changing INNER to LEFT or RIGHT, can't remember what the correct one is

 

Thanks. I'll try that.