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.

[south]

Not a master at this stuff, but try something like this:

(I was working on an oscommerce site last weekend)

<?php

$query = "SELECT products_description.products_name, products_attributes.options_values_price ".
"FROM products_description LEFT JOIN products_attributes ".
"ON products_description.products_id = products_attributes.products_id";

$result = mysql_query($query) or die(mysql_error());

// Print out the contents of each row into a table
while($row = mysql_fetch_array($result)){

?>

////////-Then you can echo the rows like so:
<? echo $row['products_name']; ?>


//////--OR, if you want to do te select distinct.

<?
$query = "SELECT DISTINCT (a.products_attributes_id), a.products_id, a.options_id, a.options_values_id, a.options_values_price, a.price_prefix
FROM products_attributes a
WHERE a.products_attributes_id = a.products_attributes_id
AND a.products_id = a.products_id
AND a.options_values_id = a.options_values_id
AND a.options_values_price = a.options_values_price
AND a.price_prefix = a.price_prefix
limit $offset, $limit";

$result = mysql_query($query);
if(!$result){
print "Database access error: abnormal termination<BR>$query<BR>".mysql_error();
exit;
}
$num_products = mysql_num_rows($result);
$i = 0;
$bgcolor = "#FFFFFF";
while($i < $num_products){

if($bgcolor == "#FFFFFF"){
$bgcolor = "#EEEEEE";
}else{
$bgcolor = "#FFFFFF";
}

$products = mysql_result($result,$i,"products_id");
$options_values_price = mysql_result($result,$i,"options_values_price");
$options_values_id = mysql_result($result,$i,"options_values_id");


$namefind = mysql_query("SELECT products_name FROM products_description WHERE products_id = '$products'");
$name= mysql_result($namefind,0);

$sizefind = mysql_query("SELECT products_options_values_name FROM products_options_values WHERE products_options_values_id = '$options_values_id'");
$size = mysql_result($sizefind,0);

?>


//////////////---- good luck!

[SGBoise]

Thanks South. It looks like that should work. I'll give it a try.

[kengreenwood]

It's actually much simpler than that. And you don't need the JOIN syntax. Use this:

select distinct files.*
from files a, transactions b
where a.product_id = b.product_id
and a.script_id = b.script_id
and b.order_number = '123456'