Results 1 to 2 of 2
  1. #1
    Join Date
    May 2002
    Location
    Philippines
    Posts
    11

    Question Unanswered: using LIKE with subqueries/JOIN (DB2)

    Hi

    Say I have these tables

    Account
    account_id char(9) ex. "ABC123456", "XYZ332211"
    (ledger_id + sequence num)

    Ledger
    ledger_id char(3) ex. "ABC", "XYZ"
    branch_id char(1) ex. "B", "C"

    I'd like to get all accounts given a branch_id. This would be easy enough if account_id were broken into ledger_id and sequence_num. I suppose it would look something like this:

    SELECT *
    FROM Account
    JOIN Ledger
    ON Account.ledger_id = Ledger.ledger_id
    WHERE branch_id = 'B'

    or

    SELECT *
    FROM Account
    WHERE Account.ledger_id = (SELECT ledger_id
    FROM Ledger
    WHERE branch_id = 'B')

    Could there be a LIKE version? something like:

    SELECT *
    FROM Account
    JOIN Ledger
    ON Account.account_id LIKE Ledger.ledger_id CONCAT '%'
    WHERE branch_id = 'B'

    or like:

    SELECT *
    FROM Account
    WHERE account_id LIKE (SELECT ledger_id CONCAT '%'
    FROM Ledger
    WHERE branch_id = 'B')

    Thanks in advance!

    IBM DB2 v8.1.0.36
    Windows 2000

  2. #2
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106
    SELECT *
    FROM Account
    JOIN Ledger ON SUBSTR(Account.account_id,1,3) = Ledger.ledger_id
    WHERE branch_id = 'B'

Posting Permissions

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