Problem with JOIN on multiple columns
Posted: Wed Aug 26, 2009 10:05 pm
Hi everyone,
I'm having a problem trying to join tables. I hope some one 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.
It's still returning rows in the files table where the script_id doesn't 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.
I'm having a problem trying to join tables. I hope some one 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.
Code: Select all
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';
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.