Re: mysql select question

[giraffemedia]

Hello

 

is it possible to select rows from one table where the data contained in a column is equal to that of another table, or is it best to use a variable as one of the references?

 

i.e

SELECT * FROM test1 WHERE test1.column1 = test2.column1

 

or

// find all the id's in the table
$query = ("SELECT id FROM test1 ORDER BY id ASC") or die(0);

$query_result = mysql_query ($query);

if (!$query_result) { 
echo ("<strong>Problem because:</strong> " . mysql_error());
}

// while loop to run through all the id's

while ($row = mysql_fetch_array($query_result, MYSQL_ASSOC)) {
$id = $row['id'];
}

// find all results from 
$results = SELECT * FROM test2 WHERE test2.column1 = $id

$query_results = mysql_query ($results);

if (!$query_results) { 
echo ("<strong>Problem because:</strong> " . mysql_error());
}

// then loop and echo the results

while ($row2 = mysql_fetch_array($query_results, MYSQL_ASSOC)) {
$id = $row2['id'];

echo $id;
}

 

Thanks

 

James

[DarkWater]

Read the Joins tutorial on the PHPFreaks home page. Should give you a pretty good understanding of the process.

[abouchoud]

Use the (LEFT|RIGHT) JOIN FOR THAT

EX.:

 

SELECT * FROM test1 LEFT JOIN test2 ON test1.column1=test2.column

[EchoFool]

surely an INNER JOIN .. ON the two table's ID would be smarter choice here? Assuming ID is the primary key ?

[giraffemedia]

The ID on the test1 is the primary key. The column on the other table I am trying to match it up with also has the ID as it's primary key but I don't want to join the table by that column.

 

Can you only do inner joins by using the primary keys from both columns?

 

I'm also reading up on the join process as we speak! 

 

Regards

 

James