Field with tricky lookup in query

[a_ud]

I need to build a query that (in principle) uses 2 tables:
TABLE 1:
Person_ID: String (for instance, ABC156)

TABLE 2:
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.

Query: Person_ID / Batch (i.e., for every Person you get which batch they are into).
This can be done either with a SELECT CASE function in the query, or SQL, or any other way as fas as it works.

Can anyone help to find a quick and clear way to do this? Thanks

 

[Xproterg]

I would try something like the following:

SELECT LAST(column_name), FIRST(column_name) FROM table_name

The above code uses functions that take that last and first record in specified columns.

or if those functions are not supported, you can try the following:

SELECT Column_Name FROM Table_Name ORDER BY Column_Name DESC LIMIT 1 AND Column_Name IN (SELECT Column_Name FROM Table_Name ORDER BY Column_Name LIMIT 1)

The above code works much like the previous example, except it only takes the first item in each column named; however, it is able to take the last column as well because it first flips the order of the records upside-down, then takes the first record again.

Hope this is what you're looking for.

Cheers

 

[a_ud]

Hi Xproterg,

Thanks for trying, but I'm not sure we're on the same page here. Let's say Table 2 has the following column/values:
First / Last / Batch
ABC100-ABC120- 1
ABC121-ABC140-2
ABC141- ABC200-3

If you run here your query1, you'd just get the column in reverse order (am I missing something?), but you'd never get the batch a given Person (let's say ABC156) belongs to.

I don't get what the 2nd query does, but it gives me a syntax error, and I don't think it can find the batch for a personID if there's no reference to field PERSON_ID in the query.............

 

[jdraw]

I did a simple mock up to test some sql.

SELECT Table2PeopleBatch.FirstP
, Table2PeopleBatch.LastP
, Table2PeopleBatch.BatchId
, TpersonIds.TPerson
FROM Table2PeopleBatch, TpersonIds
WHERE (((TpersonIds.TPerson) Between [FirstP] And [LastP]));

and it seems to work.

 

[a_ud]

Thanks, the mock up works fine in my tables too (as it should).
This is an elegant solution using SQL, and there's probably another one using a function with SELECT ..CASE in a column in the query (but this one is easier).