0

I've got an optional column (residential) in one of my tables that is giving me trouble. If a Licensee intends to work w/ residential customers, they need to take an additional course (course_topic_code C):

CREATE TABLE Licensees (
  license_num INTEGER PRIMARY KEY,
  license_level VARCHAR(50) NOT NULL,
  residential BOOLEAN
);

INSERT INTO Licensees VALUES
  (123, 'Salesperson', NULL),
  (234, 'Salesperson', TRUE),
  (567, 'Salesperson', FALSE);


CREATE TABLE Course_Requirements (
  PRIMARY KEY (license_level, residential, course_topic_code),
  license_level VARCHAR(50) NOT NULL,
  residential BOOLEAN,
  course_topic_code CHARACTER NOT NULL,
  required_minutes INTEGER NOT NULL
);

INSERT INTO Course_Requirements VALUES
  ('Salesperson', true,  'C', 90),  
  ('Salesperson', true,  'A', 180),
  ('Salesperson', false, 'A', 180),
  ('Broker',      true,  'C', 90),
  ('Broker',      true,  'A', 180),
  ('Broker',      false, 'A', 180);

The trouble is, it's valid for me to not know whether a Licensee intends to service residential customers. If the column is true they're required to take that course, but if it is false or null they're not required. This is the query I've come up with to see required courses for each Licensee:

select l.license_num, cr.course_topic_code, cr.required_minutes
from Course_Requirements cr
join Licensees l on cr.license_level = l.license_level
                and cr.residential = l.residential;

As you can see, the licensee w/ NULL for residential has no required courses:

567;"A";180
234;"C";90
234;"A";180

I might be able to devise a query that will give me what I want, but I feel that I might be modeling things incorrectly. I generally try to avoid nullable fields, but in this situation it seems appropriate. Is there a better way to model this?

2
  • What does null for residential represent? Can't you cope with 2-valued logic (true, false)? Some DBMS (only postgres?) supports the predicate IS [NOT] DISTINCT FROM which may help here
    – Lennart
    Jan 17 '19 at 7:13
  • I would define residential as NOT NULL probably with default false then you don't have to worry about null values. Jan 17 '19 at 7:25
0

question is not clear to me but this might be what you are looking for

select l.license_num, cr.course_topic_code, cr.required_minutes
from Course_Requirements cr
join Licensees l on cr.license_level = l.license_level
                and cr.residential = isnull(l.residential,1);
0

I'm not entirely clear on the problem, but you can try:

select l.license_num, cr.course_topic_code, cr.required_minutes
from Course_Requirements cr
join Licensees l 
    on cr.license_level = l.license_level
   and coalesce(cr.residential, true) = coalesce(l.residential,true);

It's probably a good idea to post a truth table like:

        true  false  null
        +-----+-----+-----+ 
true    |     |     |     |
false   |     |     |     |
null    |     |     |     |
        +-----+-----+-----+

and fill in the expected result for the join predicate

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.