Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2011
    Posts
    8

    Unanswered: Query with tricky lookup

    This must be simple, but is resisting all my attempts.

    I've got 2 tables
    TABLE 1:
    A collection of person IDs: ABC100-ABC101-ABC102.........
    <Person_ID>: [String]


    TABLE 2:
    Gives the first & last person ID for a given batch.

    <First>: [String] (1st volunteer in that batch, f.i. ABC100
    <Last>: " (last volunteer in that batch, f.i. ABC120
    <Batch>: [Integer] ('1')
    and no relationship between T1 and T2.


    So T2 would look like:

    First / Last / Batch
    ABC100 -ABC120 - 1
    ABC121 -ABC140 -2
    ABC141 - ABC200 -3

    All I want is a query that, for every Person_ID, gets the Batch (Columns: PERSONID - BATCH). How can you obtain the batch here?
    Thanks

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Try a non-equi join:

    SELECT Person_ID, Batch
    FROM T1 LEFT JOIN T2 ON T1.Person_ID >= T2.First AND T1.Person_ID <= T2.Last
    Paul

  3. #3
    Join Date
    Jun 2011
    Posts
    8
    Hi Paul,

    thanks, now it's sorted out, but as a matter of fact a non-equi join like the one you propose solves the problem.

    Cheers

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Happy to help, and welcome to the site by the way!
    Paul

Posting Permissions

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