referencing another table from within a select statement
Posted: Tue Mar 22, 2005 4:08 pm
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.
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,
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_primaryThank you for your time,