Search the Community

I have two tables as follows mysql> desc Accounts; +---------------+-----------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+-----------------+------+-----+---------+----------------+ | id | int(6) unsigned | NO | PRI | NULL | auto_increment | | Name | varchar(30) | NO | | NULL | | | Type | tinyint(1) | YES | | 0 | | | Balance | decimal(10,2) | YES | | 0.00 | | | Taxes | tinyint(1) | YES | | 0 | | | Investment | tinyint(1) | YES | | 0 | | | AccountNumber | int(11) | YES | | 0 | | +---------------+-----------------+------+-----+---------+----------------+ and the other mysql> desc Transactions; +-------------+-----------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-----------------+------+-----+-------------------+----------------+ | id | int(6) unsigned | NO | PRI | NULL | auto_increment | | Datum | timestamp | NO | | CURRENT_TIMESTAMP | | | ChequeNo | varchar(12) | YES | | NULL | | | Description | varchar(30) | NO | | NULL | | | Notes | varchar(30) | YES | | NULL | | | Value | decimal(10,2) | NO | | NULL | | | ToAccount | varchar(30) | NO | | NULL | | | FromAccount | varchar(30) | NO | | NULL | | | Cleared | tinyint(1) | YES | | 0 | | +-------------+-----------------+------+-----+-------------------+----------------+ I want to retrieve all Transaction based on the ID in the Accounts table. I have tried the following without success. mysql> select * from Transactions join Accounts on Transactions.FromAccount="Account.Name" where Accounts.ID="30"; Empty set (0.01 sec) mysql> select * from Transactions left join Accounts on Transactions.FromAccount="Account.Name" where Accounts.ID="30"; Empty set (0.00 sec) mysql> select * from Transactions left join Accounts on Accounts.ID="30" where Transactions.FromAccount="Account.Name"; Empty set (0.00 sec) this despite a select * from Transactions where FromAccount="Standard Bank"; returning 130 rows. In other words what I want is on retrieving the Account row based on its ID I want to use that Accounts Name to retrieve all pertanent records. Please make suggestions.