CODEWITHSUNDEEP
sqlsqlite
akshay
akshay

Reputation: 239

SQL Intersection of 2 queries based on common column in the same table

I have the result of 2 queries and would like to intersect these results based on a common column in both. Here time is a common column

How to achieve this? I'm using a SQLite database

Query #1:

select ltp as ltp_17sep, time 
from table 
where symbol = "AAPL" and expiry = "17SEP2020"

 +--------------------+---------------------+
 |         ltp_17sep  |        time         |
 +--------------------+---------------------+
 |              19.63 | 2020-09-11T04:25:00 |
 | 19.470000000000002 | 2020-09-11T04:30:00 |
 |              19.79 | 2020-09-11T04:35:00 |
 |              19.52 | 2020-09-11T04:40:00 |

Query# 2:

select ltp as ltp_24sep, time 
from table 
where symbol = "AAPL" and expiry = "24SEP2020"

 +--------------------+---------------------+
 |         ltp_24sep  |        time         |
 +--------------------+---------------------+
 |              17.63 | 2020-09-11T04:25:00 |
 | 18.470000000000002 | 2020-09-11T04:30:00 |
 |              16.79 | 2020-09-11T04:55:00 |
 |              17.52 | 2020-09-11T05:40:00 |

Expected result: intersection result should be based on common time column:

 +--------------------+--------------------+---------------------+
 |     ltp_17sep      |     ltp_24sep      |        time         |
 +--------------------+--------------------+---------------------+
 |              19.63 |              17.63 | 2020-09-11T04:25:00 |
 |              19.47 |              18.47 | 2020-09-11T04:30:00 |

Upvotes: 1

Views: 122

Answers (3)

forpas
forpas

Reputation: 164174

What you need is a simple self join:

select t1.ltp ltp_17sep, t2.ltp ltp_24sep, t1.time
from tablename t1 inner join tablename t2
on t2.time = t1.time
where t1.symbol = 'AAPL' and t2.symbol = 'AAPL'
  and t1.expiry = '17SEP2020' and t2.expiry = '24SEP2020'

Or cteate a CTE for each of your queries and join them:

with
  cte1 as (select ltp,time from tablename where symbol='AAPL' and expiry='17SEP2020'),
  cte2 as (select ltp,time from tablename where symbol='AAPL' and expiry='24SEP2020')
select c1.ltp ltp_17sep, c2.ltp ltp_24sep, c1.time
from cte1 c1 inner join cte2 c2
on c2.time = c1.time

See the demo.
Results:

> ltp_17sep | ltp_24sep | time               
> --------: | --------: | :------------------
>     19.63 |     17.63 | 2020-09-11T04:25:00
>     19.47 |     18.47 | 2020-09-11T04:30:00

Upvotes: 1

MikeK
MikeK

Reputation: 41

You want to do an inner join on the time column.

SELECT a1, a2, b1, b2
FROM A
INNER JOIN B on B.f = A.f
WHERE <conditions>

This takes rows where the f column are the same, time in your case and joins the columns and discards any rows where f doesn't match.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270733

One method is conditional aggregation:

select symbol, time
       max(case when expiry= '17SEP2020' then ltp end) as ltp_17sep
       max(case when expiry= '24SEP2020' then ltp end) as ltp_24sep
from table
where symbol = 'AAPL' and
      expiry ( '17SEP2020', '24SEP2020' )
group by symbol, time
having count(*) = 2;

Upvotes: 0

Related Questions