0

Apologies for the somewhat confusing title. It's difficult to summarise the issue in a couple of words. I am making a request to a database, on the following tables:

Event:

ID, startdate, offtime, meetingid reason

Meeting:

ID, description

cm_delay_reasons:

ID, reasoncode, reasondescription

The tuples returned need to match the following criteria:

  • The MeetingID in Event needs to map to an ID in Meeting.
  • The reason value in Event can not be null
  • The reason value in Event can map to a reasoncode in cm_delay_reasons.
    • The reason value can also not map, but must not be null.
    • If it isn't null, then don't display a reason description.

The reason is for the latter part is simple. Event.Reason can either be a code, indicating it matches one of our preconfigured delays for an event. It can also be plaintext, indicating that the user decided their own reason for a delay. The aim is to get both of these, and for those values that do not map, leave the reason description empty.

The SQL query I have come up with is:

select 
    m.description, e.startdate, e.offtime, e.reason, d.reasondescription 
from 
    event e, meeting m, cm_delay_reasons d 
where 
    e.meetingid = m.id 
    and (d.reasoncode = e.reason);

However, ths isn't displaying the custom reasons that a user can put in. Only the predefined ones that exist in the cm_delay_reasons table, so I changed my tact and attempted:

select 
    m.description, e.startdate, e.offtime, e.reason, d.reasondescription 
from 
    event e, meeting m, cm_delay_reasons d 
where 
    e.meetingid = m.id 
    and (e.reason is not null);

However this is causing duplicates. What I need to work out is how to marry the functionality of the two.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
christopher
  • 26,815
  • 5
  • 55
  • 89
  • Not sure if I understood completely, but if it's only a problem of duplicates, why not use `SELECT DISTINCT`? – P. Camilleri Apr 25 '14 at 08:08
  • [Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was discontinued with the ANSI-**92** SQL Standard (more than **20 years** ago) – marc_s Apr 25 '14 at 08:18
  • Yeah, I'm a sucky SQL developer. I just learn enough to get me by as I go.. which is a practise that has yielded some problems. – christopher Apr 25 '14 at 08:18
  • You need to map to the cm_delay_reasons on something, otherwise how would you now which delays is to which event? Is the ID in cm_delay_reasons a FK to the Event.ID ? – Dimt Apr 25 '14 at 08:20
  • No it's not. My colleague offered up a solution anyway, so I thought I'd present it here. It's quite simple when you know how to do it! – christopher Apr 25 '14 at 08:22

1 Answers1

0

After my far more experienced colleague saw me in distress, he helped me out, and explained the concept of a left outer join.

 SELECT m.description, e.startdate, e.offtime, e.reason, d.reasondescription 
   FROM event e
        JOIN meeting m ON e.meetingid = m.id
        LEFT OUTER JOIN cm_delay_reasons d ON e.reason = d.reasoncode
  WHERE e.reason IS NOT NULL
  ORDER BY e.startdate;
Kevin Hogg
  • 1,771
  • 25
  • 34
christopher
  • 26,815
  • 5
  • 55
  • 89