CODEWITHSUNDEEP
sqlsql-server
Merlin
Merlin

Reputation: 87

SQL Logic Needed Possible Subquery needed

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')

Upvotes: 1

Views: 56

Answers (2)

shawnt00
shawnt00

Reputation: 17915

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'
       );

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269623

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.

Upvotes: 1

Related Questions