Combine scalar query and results from a subquery that returns multiple columns-CodePudding

Is there a way to combine two queries one a scalar query and other a multiple column subquery to return the data in one row?

SELECT 
    DB_NAME() DB,
    (select SettingValue from Settings where SettingName = 'XYZ') 'Is XYZ',
    (SELECT Department.DeptName, COUNT(*) 
        FROM (SELECT DISTINCT substring(Source, len(Source) - 17, 14) AS DeptId FROM AuditLog) LogDept 
        inner join Department on LogDept.DeptId = Department.DeptId
        GROUP BY Department.DeptName)

I want the result like

DB	Is XYZ	DeptName	Count
TEST	True	FIN	20
TEST	True	HR	12

CodePudding user response：

Literally just add them to your sub-query and make it your main query.

SELECT
    DB_NAME() DB
    , (SELECT SettingValue FROM Settings WHERE SettingName = 'XYZ') 'Is XYZ'
    , D.DeptName, COUNT(*) 
FROM (
    SELECT DISTINCT substring(Source, len(Source) - 17, 14) AS DeptId
    FROM AuditLog
) LogDept AS LD
INNER JOIN Department AS D ON D.DeptId = LD.DeptId
GROUP BY D.DeptName;

And I recommend the use of short table aliases as I have demonstated.