Page 1 of 1

Problem with JOIN on multiple columns

Posted: Wed Aug 26, 2009 10:05 pm
by sgboise
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.

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

Re: Problem with JOIN on multiple columns

Posted: Wed Aug 26, 2009 10:53 pm
by califdon
I think what you want is more like:

Code: Select all

SELECT files.* FROM files, transactions
WHERE transactions.product_id=files.product_id AND transactions.script_id=files.script_id
AND transactions.order_number = '123456';
You really don't want to join the transactions table twice.

Re: Problem with JOIN on multiple columns

Posted: Thu Aug 27, 2009 3:39 am
by Darhazer
Or better:
[sql] SELECT DISTINCT files.* FROM files INNER JOIN transactions n1 ON n1.product_id = files.product_id AND n1.script_id = files.script_id WHERE transactions.order_number = '123456';[/sql]