Problem with JOIN on multiple columns

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
sgboise
Forum Newbie
Posts: 2
Joined: Mon Aug 10, 2009 5:14 pm
Location: GamePacks.org

Problem with JOIN on multiple columns

Post 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.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Problem with JOIN on multiple columns

Post 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.
User avatar
Darhazer
DevNet Resident
Posts: 1011
Joined: Thu May 14, 2009 3:00 pm
Location: HellCity, Bulgaria

Re: Problem with JOIN on multiple columns

Post 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]
Post Reply