SQL SELECT Statement Datetime Troubles with Inner Joins

Question

I have this query that returns no records because its can't match the grp.EffectiveDate specified while their are Inner Joins within the query. Database is SQL Server.

SELECT grp.GroupID, grp.GroupNumber, grp.Name, grp.Location, grp.GroupTypeID, grp.DivisionID, 
grp.MasterGroupID, grp.EffectiveDate, grp.TerminationDate, crt.[ContractNumber], pln.[PBPNumber], div.SiteName, src.Name as SourceName 
FROM [Group] grp 
    INNER JOIN [IndividualPlanDemographic] idp ON grp.GroupID = idp.IndividualPlanDemographicID 
    INNER JOIN [Plan] pln ON idp.PlanID = pln.PlanID 
    INNER JOIN [Contract] crt ON pln.ContractID = crt.ContractID 
    INNER JOIN [Division] div ON grp.DivisionID = div.DivisionID 
    INNER JOIN [SourceSystem] src ON div.SourceSystemID = src.SourceSystemID 
WHERE 1 = 1 
AND grp.EffectiveDate = '1/1/2015 12:00:00 AM' AND grp.GroupTypeID = '2' ORDER BY ContractNumber

However, if I just query the main "Group" table, it will return the correct records I'm looking for based on all the criteria.

SELECT grp.GroupID, grp.GroupNumber, grp.Name, grp.Location, grp.GroupTypeID, grp.DivisionID, 
grp.MasterGroupID, grp.EffectiveDate, grp.TerminationDate
FROM [Group] grp 
WHERE 1 = 1 
AND grp.EffectiveDate = '1/1/2015 12:00:00 AM' AND grp.GroupTypeID = '2'

Why will my query not working when using more than one table? I specifically reference the table alias before the column (grp.EffectiveDate) so I don't understand what else is wrong. As always, thank you in advance for your help.

Table structures, (minimal) sample data that exhibits your problem, expected output. These are a few of my favorite things. http://stackoverflow.com/help/how-to-ask — Tom H, Mar 03 '16 at 15:39
I've added the tag for you. Please remember to tag your RDBMS in the future. — Tom H, Mar 03 '16 at 15:39
Possible duplicate of [SQL JOIN and different types of JOINs](http://stackoverflow.com/questions/17946221/sql-join-and-different-types-of-joins) — Tab Alleman, Mar 03 '16 at 15:45

score 1 · Accepted Answer · answered Mar 03 '16 at 15:45

Perhaps one of your INNER JOIN's should be a LEFT OUTER JOIN. When using joins and you want parent records to be returned with/without children records and this is allowed by your schema (nullable FK) then you should left outer join.

SELECT grp.GroupID, grp.GroupNumber, grp.Name, grp.Location, grp.GroupTypeID, grp.DivisionID, 
grp.MasterGroupID, grp.EffectiveDate, grp.TerminationDate, crt.[ContractNumber], pln.[PBPNumber], div.SiteName, src.Name as SourceName 
FROM [Group] grp 
    INNER JOIN [IndividualPlanDemographic] idp ON grp.GroupID = idp.IndividualPlanDemographicID 
    INNER JOIN [Plan] pln ON idp.PlanID = pln.PlanID 
    INNER JOIN [Contract] crt ON pln.ContractID = crt.ContractID 
    --THE GROUP IS NOT REQUIRED TO BE IN A DIVISION RELATIONSHIP
    LEFT OUTER JOIN [Division] div ON grp.DivisionID = div.DivisionID 
    LEFT OUTER JOIN [SourceSystem] src ON div.SourceSystemID = src.SourceSystemID 
WHERE 1 = 1 
AND grp.EffectiveDate = '1/1/2015 12:00:00 AM' AND grp.GroupTypeID = '2' ORDER BY ContractNumber

score 1 · Answer 2 · answered Mar 03 '16 at 15:45

Ultimately this means that the rows that match this date and group type do not contain records in all the tables that you are INNER JOINed to.

Either your database is missing expected records, or you need to change some of these INNER JOINs to LEFT OUTER JOINs instead.

SQL SELECT Statement Datetime Troubles with Inner Joins

2 Answers2