CODEWITHSUNDEEP
sqlmerge
Iterator516
Iterator516

Reputation: 287

Merge 2 tables with conditions without creating any intermediate tables in SQL

I am using SQLite3 for this following query.

I have a table of products that looks like this:

enter image description here

I have a table of transactions that looks like this:

enter image description here

Here is my goal: For each product purchased between Jan 2016 and May 2016 (inclusive), find the number of distinct transactions. The result should look like this:

enter image description here

I know that I need to merge the "products" table with a filtered version of the "transactions" table.

I need to filter the "transactions" table like this:

select prod_id, count(distinct(trans_id)) as count 
from transactions 
where trans_dt >= '2016-01-01' and trans_dt < '2016-06-01' 
group by prod_id;

I can create a table from this query first, then merge that intermediate table with "products" to get the prod_name. However, is there a way to get my output without creating an intermediate table?

Upvotes: 0

Views: 71

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270081

You are looking for "join":

select p.prod_id, p.prod_name, count(distinct t.trans_id) as count 
from transactions t join
     products p
     on t.prod_id = p.prod_id
where t.trans_dt >= '2016-01-01' and t.trans_dt < '2016-06-01' 
group by p.prod_id, p.prod_name;

Presumably, a single transaction has only one line per product -- after all, there is a quantity column. If this is true, you can use count(*) instead of count(distinct).

Upvotes: 1

Related Questions