Jump to content

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


straygrey

Recommended Posts

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.

Link to comment
Share on other sites

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)?

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.