Select row with max value in multiple where condition

Question

I edited the following query based on this page:

Select query :

select 
    Users.Id, Users.[Name], Users.Family, Users.BirthDate,
    Users.Mobile, Users.[Description], Users.Email,
    Users.UserName, Users.fatherName,
    Users.archiveNumber, Users.[Address], Users.IsMarried,
    Users.Mazhab, 
    Cities.CityName, Religions.PersianName, Users.Date_insert, 
    Users.ImageName,
    MaghtaeTahsilis.[Name] as MaghtaeTahsilisName,
    FieldStudies.[Name] as FieldStudiesName,
    Eductionals.Institute, Eductionals.Moaddal, 
    Eductionals.FromYear, Eductionals.ToYear
from 
    Users 
left outer join 
    Eductionals on Users.id = Eductionals.UserID
left outer join 
    MaghtaeTahsilis on Eductionals.MaghtaeID = MaghtaeTahsilis.ID
left outer join 
    Cities on Users.City_Id = Cities.Id
left outer join 
    Religions on Users.Relegion_ID = Religions.ID
left outer join 
    FieldStudies on Eductionals.FieldStudy_ID = FieldStudies.ID
where 
    Users.UserName = @code_melli 
    and Eductionals.MaghtaeID = (select MAX(MaghtaeID) from Eductionals 
                                 where Eductionals.UserID = Users.Id)

This command works correctly in choosing MAX value, But if the following statement has a NULL value, no row are returned. I want to show NULL value if it is NULL.

score 1 · Accepted Answer · answered Jan 26 '19 at 21:40

Your left outer joins are being turned into inner joins by the where conditions. Your query should look like:

select u.Id, u.[Name], u.Family, u.BirthDate, u.Mobile, u.[Description], u.Email, u.UserName, u.fatherName,
       u.archiveNumber, u.[Address], u.IsMarried, u.Mazhab, c.CityName, r.PersianName, u.Date_insert, u.ImageName,
       mt.[Name] As MaghtaeTahsilisName, fs.[Name] As FieldStudiesName, e.Institute, e.Moaddal, e.FromYear, e.ToYear
from Users u left outer join
     Eductionals e
     on u.id = e.UserID and
        e.MaghtaeID = (select MAX(e2.MaghtaeID)
                       from Eductionals e2
                       where e2.UserID = u.Id
                      ) left outer join
     MaghtaeTahsilis mt
     on e.MaghtaeID = mt.ID left outer join
     Cities c
     on u.City_Id = c.Id left outer join
     Religions r
     on u.Relegion_ID = r.ID left outer join
     FieldStudies fs
     on e.FieldStudy_ID = fs.ID
where u.UserName = @code_melli ;

Conditions on the first table -- in a chain of left joins should be in the where clause. On subsequent tables in the on clauses.

You'll notice that I also added table aliases so the query is easier to write and to read.

You can also use window functions:

from Users u left outer join
     (select e2.*,
             row_number() over (partition by e2.userId order by e2.MaghtaeID desc) as seqnum
      from Eductionals e2
     ) e
     on u.id = e.UserID and
        e.seqnum = 1 left outer join
     . . .

score 0 · Answer 2 · answered Jan 26 '19 at 21:44

Reason for returning zero records when second query returns NULL is, when second query returns NULL, your SQL syntax become like this

And Eductionals.MaghtaeID=NULL

And probably Dbtable Educationals holds NULL values for field MaghtaeID. So SQL fails above syntax and thus returns zero records. Correct syntax for checking NULL values would be

And Eductionals.MaghtaeID is NULL

So please modify where condition in your query as follows which will return desired result.

where Users.UserName = @code_melli AND isnull(Eductionals.MaghtaeID,0) = isnull((select MAX(MaghtaeID) from Eductionals where Eductionals.UserID = Users.Id),0)

Select row with max value in multiple where condition

2 Answers2