Problem with JOIN on multiple columns

[SGBoise]

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';

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.

 

[chadsmith]

It sounds like it's doing that because you're doing two separate joins from the same table. Try:

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

 

[SGBoise]

Thanks. I'll try that.