CODEWITHSUNDEEP
ruby-on-railspostgresql
Danodemo
Danodemo

Reputation: 59

Which SQL Join do I use to see results from one table that are not present in set from the same table?

I have a massive table wherein records are generated each month, and the results tagged with month_of column. I need to compare these month_of result sets each month to find new activations (new records that are present this month that weren't there last month)

The goal: Get a set of results from the CURRENT MONTH where in the unique-ids are not present in the PREVIOUS MONTH.

Explained:

Last month (March), I had 10 records marked status="ACTIVE" with month_of "MARCH"

This month (April), I have 11 records marked status="ACTIVE" with month_of "APRIL"

Something like this I've already tried: if ta1 = current months's report, and ta2 = last month's report

SELECT id FROM table ta1
LEFT OUTER JOIN table ta2
ON ta1.status = ta2.status
WHERE ta1.month_of = #{current_month}
AND ta2.month_of = #{last_month}
AND ta1.status = 'ACTIVE'
AND ta2.id IS null

I need the query that would return the 1 new record with month_of "APRIL" that isn't present in the month_of "MARCH" results.

Can anyone point me at the right join to use in order to get what I'm looking for? This solution is going to apply to a table with almost a billion records.

Upvotes: 1

Views: 65

Answers (2)

Danodemo
Danodemo

Reputation: 59

THIS ARTICLE was immensely helpful in my understanding of joins and how to use them.

I actually ended up using a LEFT OUTER JOIN as follows:

SELECT se1.id FROM service_effectivenesses se1
        LEFT OUTER JOIN service_effectivenesses se2
        ON se1.vehicle_id = se2.vehicle_id
        AND se1.dealership_id = se2.dealership_id
        WHERE se1.dealership_id = #{dealershio_id}
        AND se2.id IS NULL
        AND se1.month_of = DATE('#{month_of.strftime("%Y-%m-%d")}')
        AND se2.month_of = DATE('#{month_of.strftime("%Y-%m-%d")}') - interval '1 month'
        AND se1.status = 'ACTIVE'

The #{} variables are from our Rails app, so ignore those.

Upvotes: 0

Larrihoover
Larrihoover

Reputation: 76

select id
from ta1
where month_of = (current_month)
and id not in (select id from ta1 where month_of = (last_month))
and status in ('Active')

or you could do:

select a.id
from(select id, month_of from ta1 where month_of = (current_month) and status = 'Active'
)a
left join (select id, month_of from ta1 where month_of = (last_month)
)b on a.id != b.id

Upvotes: 1

Related Questions