CODEWITHSUNDEEP
sqlsql-server
Merlin
Merlin

Reputation: 87

NULL Values not Being Returned

I have a situation where I am running a query again tables MEMBERS, MEMBERUDFS, and MEMERUDFSETUPS. Where MEMBER UDFs are User Defined Fields that are created by the system admin to store data about customers in customizable fields. The UDFSETUPS table defines the configurable fields and MEMBERUDFS stores the values of those fields if they exist.

The issue I am running into is when I pull data back, I am not getting the rows for entries that have a UDF with an ID of 17 (MEMBERUDFS.udfid = '17') that do not have an entry or are null. I tried Left Joins but it did not seem to help.

SELECT MEMBERS.scancode, MEMBERS.fname, MEMBERS.lname, MEMBERS.datejoin, MEMBERS.entrydate
     , MEMBERS.relationship, MEMBERUDFS.udfvalue
FROM MEMBERS 
LEFT OUTER JOINMEMBERUDFS 
  ON MEMBERS.memid = MEMBERUDFS.memid 
INNER JOIN MEMBERUDFSETUPS 
  ON MEMBERUDFS.udfid = MEMBERUDFSETUPS.udfid
WHERE (MEMBERS.siteid = @rvSite) 
  AND (MEMBERS.status = 'A') 
  AND (MEMBERS.relationship = 'M') 
  AND (MEMBERUDFS.udfid = '17') 
  AND (MEMBERS.datejoin BETWEEN @rvStartDate AND @rvEndDate) 
  AND (MEMBERS.mtypeid NOT IN (5, 6, 7, 11, 14, 31)) 
   OR (MEMBERS.siteid = @rvSite) 
  AND (MEMBERS.relationship = 'P') 
  AND (MEMBERUDFS.udfid = '17') 
  AND (MEMBERS.entrydate BETWEEN @rvStartDate AND @rvEndDate)

Upvotes: 1

Views: 72

Answers (1)

xQbert
xQbert

Reputation: 35323

Move the limit on the JoinMemberUDFS to the join or your nulls get excluded.

Preferred method move filter to join criteria

SELECT MEMBERS.scancode, MEMBERS.fname, MEMBERS.lname, MEMBERS.datejoin, MEMBERS.entrydate
     , MEMBERS.relationship, MEMBERUDFS.udfvalue
FROM MEMBERS 
LEFT JOIN MEMBERUDFS 
  ON MEMBERS.memid = MEMBERUDFS.memid 
 AND  MEMBERUDFS.udfid = '17'
LEFT JOIN MEMBERUDFSETUPS 
  ON MEMBERUDFS.udfid = MEMBERUDFSETUPS.udfid
WHERE (MEMBERS.siteid = @rvSite
  AND MEMBERS.status = 'A'
  AND MEMBERS.relationship = 'M' 
  AND MEMBERS.datejoin BETWEEN @rvStartDate AND @rvEndDate
  AND MEMBERS.mtypeid NOT IN (5, 6, 7, 11, 14, 31))
   OR (MEMBERS.siteid = @rvSite
  AND MEMBERS.relationship = 'P'
  AND MEMBERS.entrydate BETWEEN @rvStartDate AND @rvEndDate)

Not preferred handle when it's null and ensure it returns true for the evaluation

SELECT MEMBERS.scancode, MEMBERS.fname, MEMBERS.lname, MEMBERS.datejoin, MEMBERS.entrydate
     , MEMBERS.relationship, MEMBERUDFS.udfvalue
FROM MEMBERS 
LEFT OUTER JOINMEMBERUDFS 
  ON MEMBERS.memid = MEMBERUDFS.memid 

INNER JOIN MEMBERUDFSETUPS 
  ON MEMBERUDFS.udfid = MEMBERUDFSETUPS.udfid
WHERE (MEMBERS.siteid = @rvSite
  AND MEMBERS.status = 'A'
  AND MEMBERS.relationship = 'M' 
  AND MEMBERS.datejoin BETWEEN @rvStartDate AND @rvEndDate
  AND MEMBERS.mtypeid NOT IN (5, 6, 7, 11, 14, 31)
  AND (MEMBERUDFS.udfid = '17' or MEMBERUDFS.udfid is null)
   OR (MEMBERS.siteid = @rvSite
  AND MEMBERS.relationship = 'P'
  AND MEMBERS.entrydate BETWEEN @rvStartDate AND @rvEndDate)
  AND (MEMBERUDFS.udfid = '17' or MEMBERUDFS.udfid is null)

Upvotes: 2

Related Questions