locked
SQL Sub Query returning null RRS feed

  • Question

  • Hi Everyone, 

    I'm new on SQL, I have a T-SQL with a subquery inside a join returning a null for column (accES.iModifiedDate). Table accEvidenceScore has ID for a Subject but with multiple task completed within this subjec, I trying to find a way to only select the last record that was created using iModified.

    Would real appreciated your help..Thank you very much. Note my query below:

    SELECT 
    Con.FirstNames + ' ' + Con.Surname AS [Name],
    Email.Email as 'Email Address',
    Subj.SubjectName AS [Subject], 
    C.Name AS [Role], 
    Org.Name AS [Provider], 
    Task.iCreatedDate AS [Assigned Date], 
    accES.iModifiedDate AS [Completed Date], 
    Task.iCreatedBy AS [Assigned By],
    CASE WHEN Task.iActive = 0 THEN 'No' ELSE 'Yes' END AS [Active]
    FROM inveloper.accTask Task 
    LEFT JOIN inveloper.cmContract_Contact Cc 
    	ON Cc.iID = Task.cmContract_conContactID 
    LEFT JOIN inveloper.cmContracts C 
    	ON C.iID = CC.cmContractID 
    LEFT JOIN	[technatic2.umalusi.data].inveloper.conContact Con 
    	ON Con.iID = Cc.conContactID 
    LEFT JOIN [technatic2.umalusi.data].inveloper.conContactEmail Email
    	ON Email.conContactID = Con.iID
    LEFT JOIN	inveloper.eduSubject Subj 
    	ON Subj.iID = Task.eduSubjectID 
    LEFT JOIN	inveloper.conOrganisation Org 
    	ON Org.iID = Task.conOrganisationID
    LEFT JOIN	[technatic2.umalusi.accreditation].inveloper.accQuestionnaireAssesment accQ
    	ON	accQ.conOrganisationID = Org.iID
    LEFT JOIN (select top 1  eduSubjectID,iModifiedDate,iCreatedDate,iActive,accQuestionnaireAssesmentID
    			FROM [technatic2.umalusi.accreditation].inveloper.accEvidenceScore
    			ORDER BY iModifiedDate DESC
    			) accES
    	ON accES.accQuestionnaireAssesmentID = accQ.iID 
    WHERE   Org.iID = 7723 AND C.Name  = 'Subject Specialists (Ad-hoc)'
    


    Thursday, August 1, 2019 1:53 PM

Answers

  • Since we don't know your tables, it is difficult to say what is right and what may be wrong.

    But if accES.iModifiedDate always come back as NULL, there are two possibilities:

    1) This column is generally NULL.
    2) The join condition accES.accQuestionnaireAssesmentID = accQ.iID hits no rows.

    You will eed to dig in the data to see what might be going on.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, August 1, 2019 9:47 PM

All replies

  • Perhaps you need using MAX and group by Subject? Should it return MAX date group by Subject?

    select  eduSubjectID,MAX(iModifiedDate)iModifiedDate
    MAX(accQuestionnaireAssesmentID)accQuestionnaireAssesmentID

    FROM [technatic2.umalusi.accreditation].inveloper.accEvidenceScore

    GROUP BY eduSubjectID


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, August 1, 2019 2:00 PM
    Answerer
  • It will help to have table structure and some sample data to present your issue. We cannot access to these tables from your query so it is hard to offer help.
    Thursday, August 1, 2019 2:00 PM
  • Sorry about that. Here is a how my returned data looks

    Nathi Mabinza

    Thursday, August 1, 2019 2:51 PM
  • Since we don't know your tables, it is difficult to say what is right and what may be wrong.

    But if accES.iModifiedDate always come back as NULL, there are two possibilities:

    1) This column is generally NULL.
    2) The join condition accES.accQuestionnaireAssesmentID = accQ.iID hits no rows.

    You will eed to dig in the data to see what might be going on.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, August 1, 2019 9:47 PM
  • >> I'm new on SQL, I have a T-SQL with a subquery inside a join returning a NULL for column (accES.iModifiedDate). Table accEvidenceScore has _id for a Subject but with multiple task completed within this subjec, I trying to find a way to only select the last record [sic: rows are not records] that was created using iModified. <<

    Please read the forum rules about posting DDL. What you did post looks completely awful. Your aliases have spaces in them in violation of ISO standards, we don't put audit data like creation and modification dates in the table (the rule is not to mix data and metadata). We do not use flags in SQL; that was how we did it with assembly language 50 years ago. Back in those days we also had to put prefixes on data element names to indicate their type; I have a horrible feeling that's what you're "i" is doing.

    There is no such thing as a generic, universal, all – purpose "name" in RDBMS. It has to be the name of something in particular. See how your schema uses it for provider or for a role for a dozen other things. 

    Why are you formatting strings in the query? SQL is a tiered architecture, so display work is done in a presentation layer. Again this is not so much SQL is just bad programming on your part.

    You seem to have separated out values of attributes into their own tables, so you wind up using more left outer joins than I have ever written in production. You also don't seem to know about Camel casing and Pascal casing problems, which has nothing to do with SQL -- it's just bad programming. 

    >> Would real appreciated your help..Thank you very much.<<

    Please believe me when I tell you you need to start over. Or at least if you can find someone to help you, then please follow the forum rules and post your DDL so we can completely rewrite your schema for you.

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, August 1, 2019 10:01 PM