4 replies to this topic

[Parabola]

I used to do so much SQL... I'm out of practice. This one should be easy, yet for some reason I can't get it to work. I really need someones help please.
Here's the deal:

SELECT DISTINCT System.Name0, Programs.Version0, v_RA_System_SystemOUName.System_OU_Name0 AS OU

FROM         v_Add_Remove_Programs AS Programs INNER JOIN

                      v_R_System AS System ON Programs.ResourceID = System.ResourceID INNER JOIN

                      v_RA_System_SystemOUName ON System.ResourceID = v_RA_System_SystemOUName.ResourceID

WHERE     (Programs.DisplayName0 LIKE '%Microsoft Application Virtualization Desktop Client%') AND (Programs.Version0 LIKE '%4.6%')

Works great. But, because of SCCM's Database structure, each resourceID (computer) has multiple SystemOU rows-
Here is a small example of our output:
NAME VERSION SYSTEM OU
AMHPYXOEW1 4.6.0.20200 <DOMAIN>/THR WORKSTATIONS
AMHPYXOEW1 4.6.0.20200 <DOMAIN>/THR WORKSTATIONS/AMH
AMHPYXOEW1 4.6.0.20200 <DOMAIN>/THR WORKSTATIONS/AMH/COMMON DESKTOP AMH
ARLDTECOMMON 4.6.0.20200 <DOMAIN>/THR WORKSTATIONS
ARLDTECOMMON 4.6.0.20200 <DOMAIN>/THR WORKSTATIONS/TEST WORKSTATION OU
ARLDTECOMMON 4.6.0.20200 <DOMAIN>/THR WORKSTATIONS/TEST WORKSTATION OU/TEST2

I really just want the last entry: <DOMAIN>/THR WORKSTATIONS/<OU>
True, I could remove the first one <DOMAIN>/THR WORKSTATIONS by having where not <blah>, but that doesn't seem very efficient to me. Maybe i need a subquery? I don't know, and I feel like an id10t since I used to do this stuff all the time.....
Someone please help me...

Edited by Parabola, 28 January 2011 - 07:51 AM.
solved..

[WingedPanther]

That one seems like a good time for a subquery to me.

[Parabola]

WingedPanther said:

That one seems like a good time for a subquery to me.

That's what I figured... just was hoping there'd be a different way. I should have mentioned also though, what makes this 10 times worse- it's going to be converted to WQL :c-mad: I hate WQL....

Thankfully I have Visual Studio to work on the query with.... At least there I can run it every time I make a change easily. SCCM is not nice about that....

[Parabola]

Yep...,... I'm an id10t....
MAX() was what I needed..... I feel stupid.

Anyways, I know this is fugly, but stupid visual studio has it's auto formatting and sometimes it gets carried away... like it did this time.. but whatever

SELECT DISTINCT System.Name0, Programs.Version0, OU.OUName

FROM         v_Add_Remove_Programs AS Programs INNER JOIN

                      v_R_System AS System ON Programs.ResourceID = System.ResourceID INNER JOIN

                          (SELECT     TOP (100) PERCENT System_1.ResourceID, System_1.Name0, MAX(v_RA_System_SystemOUName.System_OU_Name0) 

                                                   AS OUName

                            FROM          (SELECT DISTINCT ResourceID, Name0

                                                    FROM          v_R_System) AS System_1 INNER JOIN

                                                   v_RA_System_SystemOUName ON System_1.ResourceID = v_RA_System_SystemOUName.ResourceID

                            GROUP BY System_1.Name0, System_1.ResourceID

                            ORDER BY System_1.Name0) AS OU ON System.ResourceID = OU.ResourceID

WHERE     (Programs.DisplayName0 LIKE '%Microsoft Application Virtualization Desktop Client%') AND (Programs.Version0 LIKE '%4.6%')

GROUP BY System.Name0, Programs.Version0, OU.OUName

[Parabola]

And I managed to clean it up, removing the subquery altogether after smacking myself in the face yet again. I seriously need some coffee this morning.... Can't produce code.... read my sig lol

SELECT DISTINCT System.Name0, Programs.Version0, MAX(OU.System_OU_Name0) AS OUName

FROM         v_Add_Remove_Programs AS Programs INNER JOIN

                      v_R_System AS System ON Programs.ResourceID = System.ResourceID INNER JOIN

                      v_RA_System_SystemOUName AS OU ON System.ResourceID = OU.ResourceID

WHERE     (Programs.DisplayName0 LIKE '%Microsoft Application Virtualization Desktop Client%') AND (Programs.Version0 LIKE '%4.6%')

GROUP BY System.Name0, Programs.Version0

ORDER BY System.Name0