query output with all possible rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

query output with all possible rows

Author

Topic

2k2er
Starting Member

20 Posts

Posted - 2011-08-11 : 12:53:28

Hi Champs,

I have a got a requirement to perform couple of joins and provide data. Below is my query...

select a.Netbios_Name0,a.User_Name0,b.Caption0 + ' ' + b.CSDVersion0 as 'OS & Service Pack',c.Serialnumber0 as 'Serial Number',
d.VariableValue0 as 'Image Version',f.VariableValue0 as 'Profile Version',g.VariableValue0 as 'Language',
h.VariableValue0 as 'SDAP',e.SMS_Installed_Sites0 as 'SMS Site Code' from v_r_system a
inner join v_gs_operating_system b on a.resourceid=b.resourceid
inner join v_gs_pc_bios c on a.resourceid=c.resourceid
left outer join v_GS_Environment_Variables0 d on a.resourceid=d.resourceid
left outer join v_GS_Environment_Variables0 f on a.resourceid=f.resourceid
left outer join v_GS_Environment_Variables0 g on a.resourceid=g.resourceid
left outer join v_GS_Environment_Variables0 h on a.resourceid=h.resourceid
inner join v_RA_System_SMSInstalledSites e on a.resourceid=e.resourceid
where a.client0=1 and a.Obsolete0=0 and a.Active0=1
and (d.Name0 in ('Image_Version')
and f.Name0 in ('Profile_Version')
and g.Name0 in ('Computer_LANG')
and h.Name0 in ('SDAPROFILE'))
order by a.Netbios_Name0

If i just run my query with the condition
d.Name0 in ('Image_Version')

i get all the data i want. When i started adding other conditions the rows are getting reduced to 3800...It is checking all the criteria's and displaying only where all the conditions comes true. But i need to display the column's as 'NULL' If it does not have any data.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-11 : 12:56:28

do you mean this?


select a.Netbios_Name0,a.User_Name0,b.Caption0 + ' ' + b.CSDVersion0 as 'OS & Service Pack',c.Serialnumber0 as 'Serial Number',
d.VariableValue0 as 'Image Version',f.VariableValue0 as 'Profile Version',g.VariableValue0 as 'Language',
h.VariableValue0 as 'SDAP',e.SMS_Installed_Sites0 as 'SMS Site Code' from v_r_system a
inner join v_gs_operating_system b on a.resourceid=b.resourceid
inner join v_gs_pc_bios c on a.resourceid=c.resourceid
left outer join v_GS_Environment_Variables0 d on a.resourceid=d.resourceid
left outer join v_GS_Environment_Variables0 f on a.resourceid=f.resourceid and f.Name0 in ('Profile_Version')
left outer join v_GS_Environment_Variables0 g on a.resourceid=g.resourceid and g.Name0 in ('Computer_LANG')
left outer join v_GS_Environment_Variables0 h on a.resourceid=h.resourceid and h.Name0 in ('SDAPROFILE')
inner join v_RA_System_SMSInstalledSites e on a.resourceid=e.resourceid
where a.client0=1 and a.Obsolete0=0 and a.Active0=1
and d.Name0 in ('Image_Version')
order by a.Netbios_Name0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

2k2er
Starting Member

20 Posts

Posted - 2011-08-11 : 13:52:18

Ohhhhhhhhh....I would not have had my third beer ;-)

Thanks Mate!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-12 : 02:29:58

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources