select rows from right table based on a column in left table

[straygrey]

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.

[Barand]

The second query should be "... FROM Accounts LEFT JOIN Transactions ..."

 

Don't use "SELECT * ", specify the columns needed.

You should be storing the Account IDs as foreign keys in the transactions table, not the account names.

 

Are you sure the account names in the tables match exactly (no spelling errors or extra whitespace)?