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