SELECT rows from a table that don't have related entries in a second table

Question

That's my first day in SQL using PostgreSQL 9.4 and I'm lost with some things. I think that I'm close but not enough:

Table definition:

CREATE TABLE DOCTOR (
    Doc_Number INTEGER,
    Name    VARCHAR(50) NOT NULL,
    Specialty   VARCHAR(50) NOT NULL,
    Address VARCHAR(50) NOT NULL,
    City    VARCHAR(30) NOT NULL,
    Phone   VARCHAR(10) NOT NULL,
    Salary  DECIMAL(8,2) NOT NULL,
    DNI     VARCHAR(10) UNIQUE
    CONSTRAINT pk_Doctor PRIMARY KEY (Doc_Number)
  );

CREATE TABLE VISIT (
    Doc_Number    INTEGER,
    Pat_Number    INTEGER,
    Visit_Date    DATE,
    Price           DECIMAL(7,2),
    CONSTRAINT Visit_pk PRIMARY KEY (Doc_Number, Pat_Number, Visit_Date),
    CONSTRAINT Visit_Doctor_fk FOREIGN KEY (Doc_Number) REFERENCES DOCTOR(Doc_Number),
    CONSTRAINT Visit_PATIENT_fk FOREIGN KEY (Pat_Number) REFERENCES PATIENT(Pat_Number)
  );

I need how to join these two queries into one:

SELECT d.City, d.Name
FROM DOCTOR d, VISIT v
WHERE d.Specialty = 'family and comunity' 
ORDER BY d.Name;

SELECT * FROM VISIT
WHERE DATE (Visit_Date)<'01/01/2012' 
            OR DATE(Visit_Date)>'31/12/2013';

I tried something like this but it doesn't work. I need the doctors of that specialty that didn't do any visit in 2012 and 2013.

SELECT City, Name
FROM DOCTOR d
WHERE d.Specialty = 'family and comunity' 
      AND NOT IN(SELECT *
        FROM VISIT 
        WHERE Visit_Date BETWEEN '2012-01-01' and '2013-12-31')
ORDER BY d.Name;

Can anyone help?

. . If you have three questions, you should probably ask them one at a time. — Gordon Linoff, Apr 11 '15 at 16:18
ok, thanks for your advice, the next time I'll do it this way — alwayslearn, Apr 11 '15 at 16:22
@alwayslearn: You can get it right *this* time. Just edit the question while you don't have answers, yet. And spawn two new question. — Erwin Brandstetter, Apr 11 '15 at 16:50
Please always provide your version of Postgres. And table definitions if the query depends on it (like in this case): what you get with `\d tbl` in psql. How are the tables `doctor` and `visit` connected? I assume you have a `doctor_id` in `visit`? You need a join condition. — Erwin Brandstetter, Apr 11 '15 at 17:31
ok I edit the post to insert the creation of tables to do more understandable, I don't need a join condition, only one query to obtain the name and city of the doctors with specialty 'family and comunity' that didn't do any visit in 2012 and 2013, ordered by name. — alwayslearn, Apr 11 '15 at 17:40

score 2 · Answer 1 · edited May 23 '17 at 12:06

2

SELECT d.name, d.city
FROM   doctor     d
LEFT   JOIN visit v ON v.doc_number = d.doc_number
                   AND v.visit_date BETWEEN '2012-01-01' AND '2013-12-31'
WHERE  d.specialty = 'family and community'  -- or 'family and comunity'?
AND    v.doc_number IS NULL
ORDER  BY d.name;

As commented you need a join condition. How are visits connected to doctors? Typically, you would have a visit.doctor_id referencing doctor.doctor_id.
Using LEFT JOIN / IS NULL to rule out doctors who have visits in said period. This is one of several possible techniques:
- Select rows which are not present in other table
Dates must be greater than the lower bound AND smaller than the upper bound. ~~OR~~ would be wrong here.
It's better to use ISO 8601 date format which is unambiguous regardless of your locale.
- field value between date range

edited May 23 '17 at 12:06

Community

1
1

answered Apr 11 '15 at 17:41

Erwin Brandstetter

605,456
145
1,078
1,228

Hi, about the first point, yes I have a Doc_Number in both tables, about the dates, yes, I need the doctors who DON'T have visits in this period, I tried your code but it returns and empty table and I'm sure that there are 2 doctors that don't have visits in this period. – alwayslearn Apr 11 '15 at 17:57
@alwayslearn: Trust me, the query is right. Maybe those doctors have another specialty or you really spelled "family and community" with just one "m"? – Erwin Brandstetter Apr 11 '15 at 18:17
I copied and pasted the specialty, and I check in my insertions one doctor that don't have visits in this period and also don't have another specialty, this should be appear in the query but not, I don't doubt about your code but I don't understand what happens. – alwayslearn Apr 11 '15 at 18:23
@alwayslearn: If you copied and pasted, then it is like I suspected, and `'family and comunity' ` is not a typo. I have `'family and community'` in my answer. – Erwin Brandstetter Apr 11 '15 at 21:23

score 2 · Answer 2 · answered Apr 11 '15 at 18:08

2

Alternative to the LEFT JOIN ... WHERE NULL construct is the plain WHERE NOT EXISTS(...) anti-join. [It is completely equivalent to erwin's query]

SELECT d.name, d.city
FROM   doctor  d
WHERE  d.specialty = 'family and community'
AND NOT EXISTS (
    SELECT 13
    FROM visit v WHERE v.doc_number = d.doc_number
                   AND v.visit_date BETWEEN '2012-01-01' AND '2013-12-31'
    )
ORDER  BY d.name;

answered Apr 11 '15 at 18:08

wildplasser

43,142
8
66
109

Hi, I tried this code by myself before and I obtain the same empty table as the previous code by Erwin – alwayslearn Apr 11 '15 at 18:25
1

As I said: they are equivalent. (and both correct). Maybe your table or data are different from what you wrote in your question? – wildplasser Apr 11 '15 at 19:05
Sorry I can't find any errors in my construction tables and my insertions are aparently correct, thanks for your help, I'll keep trying to find what's wrong. – alwayslearn Apr 11 '15 at 19:43

score 0 · Answer 3 · answered Apr 11 '15 at 21:30

You were almost there... Instead of

SELECT City, Name
FROM DOCTOR d
WHERE d.Specialty = 'family and comunity' 
      AND NOT IN(SELECT *
        FROM VISIT 
        WHERE Visit_Date BETWEEN '2012-01-01' and '2013-12-31')
ORDER BY d.Name;

try

SELECT City, Name
FROM DOCTOR d
WHERE d.Specialty = 'family and comunity' 
      AND doc_number NOT IN(SELECT doc_number -- or SELECT DISTINCT doc number - to get fewer rows from the subquery
        FROM VISIT 
        WHERE Visit_Date BETWEEN '2012-01-01' and '2013-12-31')
ORDER BY d.Name;

Just in case - table/column names are case-insensitive by default in Postgres. If you want them case sensitive - you have to write them in "" .

Hi @murison as the same of the other times i get an empty table with your code, I turn mad trying to understand what's wrong with the code. But thanks for your help. — alwayslearn, Apr 11 '15 at 21:49
Maybe that's the way it is in your data? Place some example data and desired output. — murison, Apr 12 '15 at 19:29

score 0 · Accepted Answer · answered Apr 11 '15 at 23:19

0

I finally found the solution, this is quite similar to your solutions, I post here to help another people with similar problems

SELECT City, Name
FROM DOCTOR d, VISIT v
WHERE d.Specialty = 'family and comunity' 
          AND not exists(SELECT *
          FROM visit v WHERE v.doc_number = d.doc_number
          AND v.visit_date BETWEEN '2012-01-01' AND '2013-12-31')      
GROUP BY name, city
ORDER BY d.Name;

Thank you all for your help!

answered Apr 11 '15 at 23:19

alwayslearn

103
1
9

You can do `SELECT DISTINCT city, name` instead of adding `GROUP BY` at the end. – murison Apr 13 '15 at 10:21

SELECT rows from a table that don't have related entries in a second table

4 Answers4