CODEWITHSUNDEEP
sql-serverwhere-clause
TrayS
TrayS

Reputation: 135

SQL Server: Multiple table joins with one or more WHERE clauses

I am running SQL Server and am trying to join multiple tables from CRM application but I can't get the result I need. I found this article SQL Server: Multiple table joins with a WHERE clause containing info close to what I am looking for, but I couldn't get it to work either.

I have 5 tables

Machine Table

- MachID       Name
-   1         Mach1
-   2         Mach2
-   3         Mach3

Maintenance Table

- MaintID       StatusCode
-   1                1
-   2                1

Maintenance Extension Table

- MaintID      MaintName              StartDate               EndDate              MachId
-   1         Maint Test 1    2015-02-01 05:00:00.000    2015-02-03 05:00:00.000     2
-   2         Maint Test 2    2015-02-06 05:00:00.000    2015-02-09 05:00:00.000     2

Schedule Table

- SchedID       StatusCode
-   1                1
-   2                1

Schedule Extension Table

- SchedID      SchedName              StartDate               EndDate               MachId     JobNumber
-   1         Sched Test 1    2015-01-15 05:00:00.000    2015-01-19 05:00:00.000       2        1111
-   2         Sched Test 2    2015-01-20 05:00:00.000    2015-01-23 05:00:00.000       2        2222

So I try this query as I want to get a set of data that would show a line item for a Schedule Event when there is one with NULL in the Maint Event, and likewise show the Maint Event when there is one and NULL in Schedule Event. If there are only one the two, then only show that item with NULL where nothing is.

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '1/05/15'
SET @EndDate = '5/2/15'
SELECT
     mn.name AS MachineName
    ,se.Schedname AS SchedName
    ,me.Maintname AS MaintName
    ,se.StartDate AS SchStartDate
    ,se.EndDate AS SchEndDate
    ,se.JobNumber
    ,sb.statuscode AS SchStat
    ,mb.statuscode AS MaintStat
    ,me.StartDate AS MaintStartDate
    ,me.EndDate AS MaintEndDate
FROM [machines] AS mn
INNER JOIN [MaintenanceExtension] AS me
ON mn.MachId = me.MachId
INNER JOIN [Maintenance] AS mb
ON me.MaintId = mb.MaintId
INNER JOIN [ScheduleExtension] as se
ON se.MachId = mn.MachId
INNER JOIN [Schedule] AS sb
ON sb.SchedId = se.SchedId
WHERE  se.StartDate between @StartDate and @EndDate

What I am getting, seems to include the MaintStartDate with the Schedule item and vice versa. Why doesn't the opposing columns show NULL?

MachineName    SchedName    MaintName             SchStartDate             SchEndDate           JobNumber     SchStat     MaintStat       MaintStartDate            MaintEndDate
   Mach2      SCH TEST 1    MAINT TEST 1       2015-01-15 05:00:00.000  2015-01-19 05:00:00.000   12345         1            1        2015-02-01 05:00:00.000   2015-02-03 05:00:00.000
   Mach2      SCH TEST 2    MAINT TEST 1       2015-01-20 05:00:00.000  2015-01-23 05:00:00.000   7894          1            1        2015-02-01 05:00:00.000   2015-02-03 05:00:00.000
   Mach2      SCH TEST 1    MAINT TEST 2       2015-01-15 05:00:00.000  2015-01-19 05:00:00.000   12345         1            1        2015-02-06 05:00:00.000   2015-02-09 05:00:00.000
   Mach2      SCH TEST 2    MAINT TEST 2       2015-01-20 05:00:00.000  2015-01-23 05:00:00.000   7894          1            1        2015-02-06 05:00:00.000   2015-02-09 05:00:00.000

What I am looking for is something like this:

MachineName    SchedName    MaintName             SchStartDate             SchEndDate           JobNumber     SchStat     MaintStat       MaintStartDate            MaintEndDate
   Mach2      SCH TEST 1      NULL          2015-01-15 05:00:00.000 2015-01-19 05:00:00.000       1111          1            1                NULL                    NULL
   Mach2      SCH TEST 2      NULL          2015-01-20 05:00:00.000 2015-01-23 05:00:00.000       2222          1            1                NULL                    NULL
   Mach2         NULL    MAINT TEST 1               NULL                      NULL                NULL          1            1        2015-02-01 05:00:00.000   2015-02-03 05:00:00.000
   Mach2         NULL    MAINT TEST 2               NULL                      NULL                NULL          1            1        2015-02-06 05:00:00.000   2015-02-09 05:00:00.000

Upvotes: 2

Views: 363

Answers (1)

thaagenson
thaagenson

Reputation: 28

INNER JOIN means you want to join two tables where the column is populated in both tables. Since you are wanting all results for a machine that has a MaintenanceExtension or a ScheduleExtension, try this on for size.

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '1/05/15'
SET @EndDate = '5/2/15'
SELECT
     mn.name AS MachineName
    ,se.Schedname AS SchedName
    ,me.Maintname AS MaintName
    ,se.StartDate AS SchStartDate
    ,se.EndDate AS SchEndDate
    ,se.JobNumber
    ,sb.statuscode AS SchStat
    ,mb.statuscode AS MaintStat
    ,me.StartDate AS MaintStartDate
    ,me.EndDate AS MaintEndDate
FROM [machines] AS mn
LEFT JOIN [MaintenanceExtension] AS me
ON mn.MachId = me.MachId
LEFT JOIN [Maintenance] AS mb
ON me.MaintId = mb.MaintId
LEFT JOIN [ScheduleExtension] as se
ON se.MachId = mn.MachId
LEFT JOIN [Schedule] AS sb
ON sb.SchedId = se.SchedId
WHERE  se.StartDate between @StartDate and @EndDate

This will give you all results, including machines that have neither a MaintenanceExtension nor a ScheduleExtension. If you want to limit to machines that have one or the other, then you need this where clause.

WHERE  se.StartDate between @StartDate and @EndDate
  AND  (me.MachId IS NOT NULL OR se.MachID IS NOT NULL)

Finally, if you want to exclude machines that have both, you need this where clause.

WHERE  se.StartDate between @StartDate and @EndDate
  AND  (me.MachId IS NOT NULL OR se.MachID IS NOT NULL)
  AND  (me.MachId IS NULL OR se.MACHId IS NULL)

Upvotes: 1

Related Questions