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

debugcn 에 게시 Dev

Carl M

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.

Paul Grimshaw

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'

이 기사는 인터넷에서 수집됩니다. 재 인쇄 할 때 출처를 알려주십시오.

침해가 발생한 경우 연락 주시기 바랍니다[email protected] 삭제

에서 수정2021-06-1

몇 마디 만하겠습니다

0리뷰

로그인참여 후 검토

Related 관련 기사

기사

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

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

How to update table multiple times based on another table

Returning null values with multiple table in a query

Sql copying a data from table to another table

Update MySQL table based on results for joining to tables

Limit results based on the dates of a related table

Insert another hierarchy into a sql table

Insert selected value to table from another table multiple times

insert multiple rows mysql from another table

See if multiple entries exist in another table

Excel - sum values based on a column that match another column in another table

Updating sql table with results from left join (sql server)

MySql multiple select from two tables and join their results to a third table

Multiple sql joins from same reference table

SQL how to use multiple joins on the same table

SQL Multiple Joins on same table with where clause

SQL Server : convert multiple XML to table

Update statement to set a column based the maximum row of another table

How to do autoincrement based on last value from another table?

PARTITION BY multiple column while inserting data from another table

MYSQL select count of multiple ids occurring in another table

MySql select all rows in one table based on MAX value in another table

new variable in data.table R based on multiple criteria

How to print a table of information in Java

Merge value of two colums and insert in to another table in ms sql

SQL COUNT to count all the numbers from another table

SQL COUNT to count all the numbers from another table

SQL server : Inserting/updating missing data from one table to another

C# Linq CopyToDataTable based of Linq To Sql table class structure

SQL SELECT Data from other table based on column value