referencing another table from within a select statement

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
bellesdad
Forum Newbie
Posts: 3
Joined: Tue Mar 22, 2005 3:53 pm

referencing another table from within a select statement

Post by bellesdad »

Thank you for taking the time to read this.

I have two tables: Accounts and AccountTypes. Accounts.accounttypeID_foriegn is a field which coresponds to AccountTypes.accounttypeID_primary. I want to list the contents of Accounts, but instead of displaying the integer: Accounts.accounttypeID_foriegn, I want to display it's name AccountTypes.name. The following SQL statement lists everything in Accounts and displays the AccountType.name, but also includes rows from the AccountTypes table. I don't want to include rows from the AccountTypes table, I only want to use it to look up the names.

Code: Select all

SELECT
  Accounts.ID AS accountid,
  Accounts.name AS accountname,
  Accounts.accounttypeID_foriegn,
  Accounts.balance AS accountbalance,
  AccountTypes.accounttypeID_primary,
  AccountTypes.name AS accounttypename
FROM Accounts, AccountTypes
WHERE Accounts.accounttypeID_foriegn = AccountTypes.accounttypeID_primary
There is 1 record in Accounts and 2 records in AccountTypes. When I run this, I get 2 record returned. How can I stop it from listing the contents of AccountTypes?

Thank you for your time,
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

post an export of these tables and their structures please. It appears that accounttypeID_foreign matches accounttypeID_primary in both cases.
bellesdad
Forum Newbie
Posts: 3
Joined: Tue Mar 22, 2005 3:53 pm

Post by bellesdad »

Code: Select all

CREATE TABLE `Accounts` (
  `ID` tinyint(2) unsigned NOT NULL auto_increment,
  `name` text NOT NULL,
  `accounttypeID_foriegn` tinyint(2) NOT NULL default '0',
  `balance` decimal(9,2) NOT NULL default '0.00',
  PRIMARY KEY  (`ID`)
) TYPE=MyISAM AUTO_INCREMENT=2 ;

CREATE TABLE `AccountTypes` (
  `accounttypeID_primary` tinyint(2) NOT NULL default '0',
  `name` text NOT NULL
) TYPE=MyISAM;
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

yup, accounttypeID_foreign matches accounttypeID_primary in both cases, very likely. Your AccountTypes table doesn't have PRIMARY nor UNIQUE keys set for accounttypeID_primary. The field should have auto_increment too.
bellesdad
Forum Newbie
Posts: 3
Joined: Tue Mar 22, 2005 3:53 pm

Post by bellesdad »

You are a genius! Yes, I forgot to make the id field primary and didn't even catch it. It works beautifully now, thanks to you.
Post Reply