CODEWITHSUNDEEP
sqloracle-databasesubquery
HalfWebDev
HalfWebDev

Reputation: 7638

Difficulty understanding this query oracle sql

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;

Upvotes: 0

Views: 87

Answers (4)

bvr
bvr

Reputation: 4826

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;

Upvotes: 0

Dmitrii Bychenko
Dmitrii Bychenko

Reputation: 186668

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;

Upvotes: 3

StanislavL
StanislavL

Reputation: 57381

The subquery returns unique cobinations of bu, hqname, location

Then they are grouped and only locations where there are more than one hqname remain.

Upvotes: 1

Devart
Devart

Reputation: 121902

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

Upvotes: 0

Related Questions