CODEWITHSUNDEEP
sql
Carl M
Carl M

Reputation: 215

SQL - Searching in a table based on information in another table and returning multiple results

I have one table 'trace' which contains the following information:

|  galaxyId  |  lastProgenitorId  |
-----------------------------------
|  0         |  27                |
|  2890      |  3001              |
|  ...       |  ...               |
|  189279    |  190056            |
-----------------------------------

And I have another table 'main' which includes the following:

|  galaxyId  |  stellarMass  |  umag  |  imag  |
------------------------------------------------
|  25        |  10.5         |  -21   |  -22   |
|  2901      |  10.8         |  -23   |  -21   |
|  3000      |  10.0         |  -22   |  -21   |
|  ...       |  ...          |  ...   |  ...   |
|  200000    |  10.1         |  -22   |  -22   |
------------------------------------------------

What I wish to do is, for each trace.galaxyId, retrieve ALL the corresponding rows in maintable where the following condition is met:

main.galaxyId BETWEEN trace.galaxyId AND trace.lastProgenitorId

This could return multiple results, such that the results would be (from the example above):

|  trace.galaxyId  |  main.galaxyId  |  main.umag  |  main.imag  |
------------------------------------------------------------------
|  0               |  25             |  -21        |  -22        |
|  2890            |  2901           |  -23        |  -21        |
|  2890            |  3000           |  -22        |  -21        |
------------------------------------------------------------------

I feel like this is an easy one but my brain just can't see how to do this! I feel like PARTITION BY is needed? Any help greatly appreciated. Thanks in advance.

Upvotes: 0

Views: 41

Answers (1)

Paul Grimshaw
Paul Grimshaw

Reputation: 21034

Use a join:

SELECT 
    t.GalaxyId As TraceGalaxyId,
    m.GalaxyId as MainGalaxyId,
    m.umag,
    m.imag 
FROM trace
JOIN main m 
ON m.galaxyId BETWEEN t.GalaxyId AND t.lastProgenitorId AND m.snap = 'Specific Value'

Upvotes: 1

Related Questions