Page 1 of 1

Cross table SELECT in MySQL

Posted: Fri Mar 03, 2006 8:09 am
by ska
Hi, I've been using MySQL for a while now but one habit I have is bugging me, as I think I can probably simplify the way I work. I think it involves cross-table joins but I've never quite 'got' this. For example, a value in one table relates to a value in another table. I have to get the value in the first table in order to retrieve the value in the second.

For example, you want to get the currency symbol for a particular country.

Code: Select all

//get country
$sql = "SELECT country FROM customer_profiles WHERE (custid = '$custid')";
$result = mysql_query( $sql );
$row = mysql_fetch_assoc( $result );
extract($row, EXTR_OVERWRITE );

// get currency symbol		
$sql = "SELECT currency_symbol FROM currencies WHERE (associated_country = '$country')";
$result = mysql_query( $sql );
$row = mysql_fetch_assoc( $result );
extract($row, EXTR_OVERWRITE );
Is there a way of combining this into one SELECT statement? If so, what is it?

Thanks for your help.

Posted: Fri Mar 03, 2006 8:18 am
by feyd

Code: Select all

SELECT `currencies`.`currency_symbol`
FROM `customer_profiles`
INNER JOIN `currencies`
ON `currencies`.`associated_country` = `customer_profiles`.`country`
WHERE `customer_profiles`.`custid` = '$custid'

thanks

Posted: Fri Mar 03, 2006 10:14 am
by ska
Thanks, that sounds like the badger. Will give it a go

Cheers.