CODEWITHSUNDEEP
sqldistinctunion
ryvantage
ryvantage

Reputation: 13479

Select distinct values from union

I have three tables (sales orders, invoices, purchase orders)

sales_order
------------
so_id (primary key)
item_id (foreign key)
entry_date

invoice
------------
invc_id (primary key)
item_id (foreign key)
entry_date

purchase_order
------------
po_id (primary key)
item_id (foreign key)
entry_date

and they all refer to one central table (item):

item_id (pk)

I am trying to write a sql query that will return all items with activity within a date range.

This is what I've come up with:

select distinct item_id from sales_order where entry_date between ? and ?
union
select distinct item_id from invoice where entry_date between ? and ?
union
select distinct item_id from purchase where entry_date between ? and ?

I think this is the correct solution, but I'm not sure how to test it.

Question 1: Does the "distinct" keyword apply to all of the statements or only to each statement? i.e., will each query return a distinct set but when you "union" them together it can show duplicates?

Question 2: Is there a way to return the total (unique) item count (as a separate query)? Like:

select count(
    select distinct item_id from sales_order where entry_date between ? and ?
    union
    select distinct item_id from invoice where entry_date between ? and ?
    union
    select distinct item_id from purchase where entry_date between ? and ?
)

??

Upvotes: 0

Views: 1641

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

The distinct is redundant. I usually write such as query as:

select item_id from sales_order where entry_date between ? and ?
union  -- intentionally removing duplicates
select item_id from invoice where entry_date between ? and ?
union
select item_id from purchase where entry_date between ? and ?;

To return the total count, you can use a subquery:

select count(*)
from (select item_id from sales_order where entry_date between ? and ?
      union  -- intentionally removing duplicates
      select item_id from invoice where entry_date between ? and ?
      union
      select item_id from purchase where entry_date between ? and ?
     ) i;

Upvotes: 1

Related Questions