How Do I Write A Simple Select Query Instead Of Using Views?

Find patients who visited two different doctors of the same specialty in the same day. Example database: Click here to view the sample data script in SQL Fiddle. CREATE VIEW Distin

Solution 1:

What you need is a HAVING clause to find COUNT(*) = 2 after grouping by date and specialty. In fact, no nesting should even be necessary. (I have also replaced your implicit join by comma separated FROM clause with an explicit JOIN, which is the more preferred modern syntax).

SELECT 
  v.pid,
  d.speciality,
  v.date,
  COUNT(COUNT DISTINCT d.did) AS numvisits
FROM 
  visits v
  JOIN Doctors d ON v.did = d.did
GROUPBY v.pid, d.speciality, v.date
HAVING COUNT(COUNT DISTINCT d.did) = 2
/* Note - depending on your RDBMS, you may
   be able to use the count aliasas
HAVING numvisits = 2 
   MySQL allows this, for ex, but MS SQL Server doesn't and I think Oracle doesn't */

The SELECT list here and GROUP BY should produce the patient id, specialty, date, and the number of visits for the aggregate combination of those 3 columns. The HAVING clause then limits it to only those with 2 visits for the group.

To pull only the patients from this, wrap it in a subquery:

SELECT Patients.* 
FROM Patients JOIN (
  SELECT 
    v.pid,
    d.speciality,
    v.date,
    COUNT(COUNT DISTINCT d.did) AS numvisits
  FROM 
    visits v
    JOIN Doctors d ON v.did = d.did
  GROUPBY v.pid, d.speciality, v.date
  HAVING COUNT(COUNT DISTINCT d.did) = 2
) subq ON Patients.pid = subq.pid

Solution 2:

A bit late after Siva's answer, but here's my query:

SELECT v.pid, d.speciality, count(DISTINCT d.did) as cnt
  FROM Visits v
  JOIN Doctors d ON v.did = d.did
 GROUPBY v.pid, d.speciality, v.date
HAVING count(DISTINCT d.did) = 2;

It produces exactly the same output as the initial OP's query without views.

Solution 3:

WITH
dv1 as
(
    SELECT v.vid, v.pid, d.speciality, v.dateFROM Visits v, Doctors d
    WHERE d.did=v.did
    GROUPBY v.pid,v.did,v.date;
),
dv2 as
(
    SELECT dv.pid,dv.speciality,dv.date, COUNT(dv.vid) as countv
    FROM dv1
    GROUPBY dv.pid,dv.speciality,dv.date;
)
SELECT dv2.pid, dv2.speciality
FROM dv2
WHERE dv2.countv=2;

Solution 4:

You can use a nested CTE (if you are not on mysql, that is...) A CTE can be seen as an immediate view, which only exists for the duration of the statement. (NOTE: untested)

WITH DistinctVisits2 AS (
    WITH DistinctVisits AS (
        SELECT v.vid,v.pid,d.speciality,v.dateFROM Visits v
        JOIN Doctors d ON d.did=v.did
        GROUPBY v.pid,v.did,v.date
        )
    SELECT dv.pid,dv.speciality,dv.date, COUNT(dv.vid) as countv
    FROM DistinctVisits dv
    GROUPBY dv.pid,dv.speciality,dv.date
    )
SELECT dv2.pid,dv2.speciality
FROM DistinctVisits2 dv2
WHERE dv2.countv=2
    ;