How can I join tables using information from different rows?-CodePudding

I have two similar tables that I would like to join based on their Tote and their content (combined with date, assume uniqueness). The totearrivalTimestamp can be used to identify unique totes within the table. The content is in a different order (needs to be sorted somehow), and then grouped by tote and date before being joined, but I'm struggling how this is done best.

The first table looks like:

Date       TotearrivalTimestamp Tote     content  location
2021-01-01 13:00                A        hat      1        <-- first tote arriving at 13:00
2021-01-01 13:00                A        cat      1        <-- first tote arriving at 13:00
2021-01-01 14:00                B        toy      1        <-- second tote arriving at 14:00
2021-01-01 14:00                B        cat      1        <-- second tote arriving at 14:00
2021-01-01 15:00                A        toy      1        <-- third tote (reused) arriving at 15:00
2021-01-01 13:00                A        toy      1
2021-01-02 13:00                A        hat      1
2021-01-02 13:00                A        cat      1

The second table looks like:

Date       ToteendingTimestamp Tote     content  location
2021-01-01 20:00               B        cat      2        <-- second tote ending at 20:00
2021-01-01 19:00               A        cat      1        <-- first tote ending at 19:00
2021-01-01 19:00               A        hat      1        <-- first tote ending at 19:00
2021-01-01 20:00               B        toy      2        <-- second tote ending at 20:00
2021-01-01 14:00               A        toy      1
2021-01-02 14:00               A        hat      1
2021-01-02 14:00               A        cat      1 
2021-01-01 16:00               A        toy      1        <-- third tote (reused) ending at 16:00

What I would like as a result:

Date       Tote     TotearrivalTimestamp ToteendingTimestamp location_first_table location_second_table
2021-01-01 A        13:00                19:00               1                    1
2021-01-01 B        14:00                20:00               1                    1
2021-01-01 A        15:00                16:00               1                    2
2021-01-02 A        13:00                14:00               1                    1

There are more than a million rows (but safe to assume uniqueness of totes though if with date and content, because there are over 300.000 contents in reality).

So what I would like is to join the Totes. For example: the first Tote would be joined using '2021-01-01'(Date), 'hat'(content), 'cat'(content) and 'A'(Tote).

CodePudding user response：

select first.date as Date, first.tote, first.totearrivaltimestamp, second.toteendingtimestamp, first.location as location_first_table, second.location as location_second_table
from first inner join second on first.tote = second.tote and first.content = second.content

CodePudding user response：

select f.date
,f.tote
, f.totearrivaltimestamp
, s.toteendingtimestamp
, f.location
, s.location
from first f
,(INNER JOIN second s on f.date = s.date and f.tote = s.tote and f.content = s.content)