I just want to know the internal execution of this query especially how the values in from subquery are being used
SELECT bu, location FROM
(
SELECT DISTINCT bu, hqname, location FROM DOCTOR
UNION
SELECT DISTINCT bu, hqname, location FROM CHEMIST
UNION
SELECT DISTINCT bu, hqname, location FROM STOCKIST
)
GROUP BY bu, location
HAVING COUNT (DISTINCT hqname) > 1;
Commented SQL
SELECT -- bu and location from doctors, chemists and stockists
-- (see inner query)
bu,
location
FROM ( -- All doctors, chemists and stockists
-- with duplicates removed:
-- e.g. if a person is a chemist and a doctor, only one record is preserved
SELECT DISTINCT bu,
hqname,
location
FROM DOCTOR
UNION
SELECT DISTINCT bu,
hqname,
location
FROM CHEMIST
UNION
SELECT DISTINCT bu,
hqname,
location
FROM STOCKIST)
GROUP BY -- combining "bu" and "location" (for HAVING)
bu,
location
-- choose these records only that have more than one hqName
-- for the same bu and location, e.g.
-- hqName bu loc
-- x 1 2
-- x 1 2 <-- second hqName ("x") for the same bu and loc (1, 2)
HAVING COUNT (DISTINCT hqname) > 1;
The subquery returns unique cobinations of bu, hqname, location
Then they are grouped and only locations where there are more than one hqname remain.
Try this query without using DISTINCT which return the same results -
SELECT bu, location
FROM (
SELECT bu, hqname, location FROM DOCTOR
UNION
SELECT bu, hqname, location FROM CHEMIST
UNION
SELECT bu, hqname, location FROM STOCKIST
) AS t
GROUP BY bu, location
HAVING COUNT (DISTINCT hqname) > 1
In below query the result of three tables 'DOCTOR','CHEMIST' and 'STOCKIST' combined to one result (treated as table) using UNION
SELECT DISTINCT bu, hqname, location FROM DOCTOR
UNION
SELECT DISTINCT bu, hqname, location FROM CHEMIST
UNION
SELECT DISTINCT bu, hqname, location FROM STOCKIST
Aggregated data is fetched using GROUP BY with count of hqname > 1 for every (bu, location ) group as below
GROUP BY bu, location
HAVING COUNT (DISTINCT hqname) > 1;
and finally you will get unique bu, location from from the result set simply put as
SELECT bu, location FROM Resultset
GROUP BY bu, location
HAVING COUNT (DISTINCT hqname) > 1;