Cross table SELECT in MySQL

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
ska
Forum Commoner
Posts: 41
Joined: Mon Sep 05, 2005 4:54 pm

Cross table SELECT in MySQL

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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'
ska
Forum Commoner
Posts: 41
Joined: Mon Sep 05, 2005 4:54 pm

thanks

Post by ska »

Thanks, that sounds like the badger. Will give it a go

Cheers.
Post Reply