Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2008
    Location
    Midrand, South Africa
    Posts
    22

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

    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.

  2. Best Answer
    Posted by healdem

    "First attempt is almost right
    Don't delimit the other table / column (account.Name). As that tells the SQL engine to match a string / text literal."


  3. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    First attempt is almost right
    Don't delimit the other table / column (account.Name). As that tells the SQL engine to match a string / text literal.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #3
    Join Date
    Oct 2008
    Location
    Midrand, South Africa
    Posts
    22
    That gives me the following or did I misunderstand you?
    mysql> select * from Transactions join Accounts on Transactions.FromAccount=Account.Name where Accounts.ID="30";
    ERROR 1054 (42S22): Unknown column 'Account.Name' in 'on clause'
    mysql>

  5. #4
    Join Date
    Oct 2008
    Location
    Midrand, South Africa
    Posts
    22
    And if I spelt Accounts correctly it would work. Thanks for the help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •