SQL Logic Needed Possible Subquery needed

Question

I am building a query where I need to retrieve all of the records from the MEMBERS table where the join date (datejoin) is between two dates, one of a certain membership type (mtypeid), and from a particular location (siteid).

THEN, I need to pull back all of the rows from the SCSCHEDULER (this table stores all appointments) table where the appointments for these members that are scheduled past a certain date, are of a certain type of appointment and are still active.

I have the following but I believe I need to place some of this logic in a subquery because I am only returning data for members that have appointments. I need to return the data from the Member table at the very least for members that do not have any appointments and the member and scscheduler data for those that do have appointments.

SELECT        MEMBERS.scancode, MEMBERS.lname, MEMBERS.fname, MEMBERTYPES.description, MEMBERS.status, MEMBERS.datejoin, EMPLOYEES.lname AS Expr1, 
                         EMPLOYEES.fname AS Expr2, SCSCHEDULES.scheduledatefrom, SCSCHEDULES.timeduration, SCSCHEDULES.scheduledescription, 
                         SCSCHEDULES.schedulestatus
FROM            MEMBERS LEFT OUTER JOIN
                         SCSCHEDULES ON MEMBERS.memid = SCSCHEDULES.memid INNER JOIN
                         MEMBERTYPES ON MEMBERS.mtypeid = MEMBERTYPES.mtypeid INNER JOIN
                         EMPLOYEES ON MEMBERS.employeeid = EMPLOYEES.employeeid
WHERE        (MEMBERS.datejoin BETWEEN @rvStartDate AND @rvEndDate) AND (MEMBERS.siteid = '779') AND (MEMBERS.mtypeid = '1' OR
                         MEMBERS.mtypeid = '10' OR
                         MEMBERS.mtypeid = '12' OR
                         MEMBERS.mtypeid = '28' OR
                         MEMBERS.mtypeid = '32' OR
                         MEMBERS.mtypeid = '33' OR
                         MEMBERS.mtypeid = '34' OR
                         MEMBERS.mtypeid = '35' OR
                         MEMBERS.mtypeid = '36' OR
                         MEMBERS.mtypeid = '40' OR
                         MEMBERS.mtypeid = '48') AND (SCSCHEDULES.scheduledatefrom >= @rvStartDate) AND (SCSCHEDULES.scheduledescription = 'First' OR
                         SCSCHEDULES.scheduledescription = 'Second') AND (SCSCHEDULES.schedulestatus = '1')

side note @Merlin, you can shorting your WHERE MEMEBER.mtypeid = ...or... by using IN(). It's a lot less typing. msdn.microsoft.com/en-us/library/ms177682.aspx — S3S
– S3S, Commented Jul 5, 2016 at 20:20

shawnt00 · Accepted Answer · 2016-07-06 04:43:51Z

When you try to filter against SCSCHEDULES in the WHERE clause (and so after the outer join,) you end up dropping the rows that you wanted to keep. They need to be placed in the join condition or wrapped up inside a derived table as below:

SELECT m.scancode, m.lname, m.fname, mt.description, m.status, m.datejoin,
       e.lname AS Expr1, e.fname AS Expr2,
       s.scheduledatefrom, s.timeduration, s.scheduledescription, s.schedulestatus
FROM   MEMBERS AS m
       LEFT OUTER JOIN
       (
       SELECT * FROM SCSCHEDULES
       WHERE
               scheduledatefrom >= @rvStartDate
           AND scheduledescription IN ('First', 'Second')
           AND schedulestatus = '1'
       ) AS s
           ON m.memid = s.memid
       INNER JOIN MEMBERTYPES AS mt
           ON m.mtypeid = mt.mtypeid
       INNER JOIN EMPLOYEES AS e
           ON m.employeeid = e.employeeid
WHERE      m.datejoin BETWEEN @rvStartDate AND @rvEndDate
       AND m.siteid = '779'
       AND m.mtypeid IN (
           '1', '10', '12', '28', '32', '33', '34', '35', '36', '40, '48'
       );

Gordon Linoff · Accepted Answer · 2016-07-05 22:20:32Z

In a LEFT JOIN, conditions on all but the first table should go in the ON clause rather than the WHERE clause:

SELECT m.scancode, m.lname, m.fname, mt.description, m.status, m.datejoin,
       e.lname AS Expr1, e.fname AS Expr2,
       s.scheduledatefrom, s.timeduration, s.scheduledescription, s.schedulestatus
FROM MEMBERS m LEFT OUTER JOIN
     SCSCHEDULES s
     ON MEMBERS.memid = s.memid AND scheduledatefrom >= @rvStartDate AND
        scheduledescription IN ('First', 'Second') AND
        schedulestatus = '1' INNER JOIN
     MEMBERTYPES mt
     ON m.mtypeid = mt.mtypeid INNER JOIN
     EMPLOYEES e
     ON m.employeeid = e.employeeid
WHERE m.datejoin BETWEEN @rvStartDate AND @rvEndDate AND
      m.siteid = '779' AND
      m.mtypeid IN ('1', '10', '12', '28', '32', '33', '34', '35', '36', '40, '48');

Note: If your ids are numbers then you should drop the single quotes. Mixing types can just confuse the optimizer.

Also note that table aliases make the query easier to write and to read.

Collectives™ on Stack Overflow

SQL Logic Needed Possible Subquery needed

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related