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

    Question Unanswered: using LIKE with subqueries/JOIN

    ** sorry if this is in the wrong thread(not SQL thread), I'm was thinking the feature I'm asking for is DB2 specific

    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
    Last edited by patlv23; 06-29-04 at 10:47. Reason: Maybe question should be in SQL thread? But

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    May be this:
    Code:
    SELECT *
    FROM Account
    JOIN Ledger
    ON SUBSTR(Account.account_id,1,3) = Ledger.ledger_id
    WHERE Ledger.branch_id = 'B'

  3. #3
    Join Date
    May 2002
    Location
    Philippines
    Posts
    11
    thanks n_i! that was exactly what I was looking for

Posting Permissions

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