Little query help

Hi,

I’ve done a query which is quite working and and could use a little advice and a once over.

In short it’s a query that first provides data, and then a subset query atht filters the data on last logge don time. I know i’m missing some thing but if some has some time to quickly look over and advise that would be much appreciated.

SELECT Distinct
v_R_System.Name0 AS ‘Netbios Name’,
v_R_System.User_Name0 as ‘User Name’,
v_GS_SYSTEM_CONSOLE_USER.LastConsoleUse0 AS ‘Last logged On’,
v_R_System.AD_Site_Name0 AS ‘AD Site’,
v_GS_Advanced_Desktop_Moni0.MonitorManufacturer0 AS ‘Monitor Manufacturer’,
v_GS_Advanced_Desktop_Moni0.SerialNumber0 AS ‘Serial Number’,
v_GS_Advanced_Desktop_Moni0.MonitorType0 AS ‘Monitor Type’,
v_GS_Advanced_Desktop_Moni0.DisplayAdapterDriverVersion0 AS ‘Display Adapter Driver Version’,
v_R_System.Creation_Date0 AS ‘Creation Date’,
v_GS_OPERATING_SYSTEM.Caption0 as ‘OS’,
v_GS_OPERATING_SYSTEM.CSDVersion0 as ‘Service Pack’,
v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 as ‘Total Physical Memory’,
v_GS_SYSTEM_CONSOLE_USER.SystemConsoleUser0 AS ‘Last Logon Name’

FROM
v_R_System
LEFT JOIN v_GS_Advanced_Desktop_Moni0 ON v_GS_Advanced_Desktop_Moni0.ResourceID = v_R_System.ResourceID
LEFT JOIN v_GS_OPERATING_SYSTEM ON v_GS_OPERATING_SYSTEM.ResourceID = v_R_System.ResourceID
LEFT JOIN v_GS_ACE_Build_Version0 ON v_GS_ACE_Build_Version0.ResourceID = v_R_System.ResourceID
LEFT JOIN v_GS_SYSTEM_CONSOLE_USER ON v_GS_SYSTEM_CONSOLE_USER.ResourceID = v_R_System.ResourceID
LEFT JOIN v_GS_X86_PC_MEMORY ON v_GS_X86_PC_MEMORY.ResourceID = v_R_System.ResourceID

WHERE v_R_System.Name0 like ‘xxx%’
or v_R_System.Name0 like ‘xxx%’

—ORDER BY v_R_System.Name0

JOIN (
SELECT v_R_System.Name0 AS ‘Netbios Name’, MAX(v_GS_SYSTEM_CONSOLE_USER.LastConsoleUse0)AS ‘Last logged On’
FROM v_R_System
LEFT JOIN v_GS_SYSTEM_CONSOLE_USER ON v_GS_SYSTEM_CONSOLE_USER.ResourceID = v_R_System.ResourceID
GROUP BY Name0)

two suggestions

first, you will help yourself immensely by learning some type (it does not matter very much which type, just as long as you use one) of formatting convention

here’s how i would format your query –

SELECT DISTINCT
       v_R_System.Name0 AS 'Netbios Name'
     , v_R_System.User_Name0 as 'User Name'
     , v_GS_SYSTEM_CONSOLE_USER.LastConsoleUse0 AS 'Last logged On'
     , v_R_System.AD_Site_Name0 AS 'AD Site'
     , v_GS_Advanced_Desktop_Moni0.MonitorManufacturer0 AS 'Monitor Manufacturer'
     , v_GS_Advanced_Desktop_Moni0.SerialNumber0 AS 'Serial Number'
     , v_GS_Advanced_Desktop_Moni0.MonitorType0 AS 'Monitor Type'
     , v_GS_Advanced_Desktop_Moni0.DisplayAdapterDriverVersion0 AS 'Display Adapter Driver Version'
     , v_R_System.Creation_Date0 AS 'Creation Date'
     , v_GS_OPERATING_SYSTEM.Caption0 as 'OS'
     , v_GS_OPERATING_SYSTEM.CSDVersion0 as 'Service Pack'
     , v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 as 'Total Physical Memory'
     , v_GS_SYSTEM_CONSOLE_USER.SystemConsoleUser0 AS 'Last Logon Name'
  FROM v_R_System
LEFT OUTER
  JOIN v_GS_Advanced_Desktop_Moni0
    ON v_GS_Advanced_Desktop_Moni0.ResourceID = v_R_System.ResourceID
LEFT OUTER
  JOIN v_GS_OPERATING_SYSTEM
    ON v_GS_OPERATING_SYSTEM.ResourceID = v_R_System.ResourceID
LEFT OUTER
  JOIN v_GS_ACE_Build_Version0
    ON v_GS_ACE_Build_Version0.ResourceID = v_R_System.ResourceID
LEFT OUTER
  JOIN v_GS_SYSTEM_CONSOLE_USER
    ON v_GS_SYSTEM_CONSOLE_USER.ResourceID = v_R_System.ResourceID
LEFT OUTER
  JOIN v_GS_X86_PC_MEMORY
    ON v_GS_X86_PC_MEMORY.ResourceID = v_R_System.ResourceID
 WHERE v_R_System.Name0 like 'xxx%'
    OR v_R_System.Name0 like 'xxx%'
---ORDER BY v_R_System.Name0
INNER
  JOIN ( SELECT v_R_System.Name0 AS 'Netbios Name'
              , MAX(v_GS_SYSTEM_CONSOLE_USER.LastConsoleUse0) AS 'Last logged On'
           FROM v_R_System
         LEFT OUTER
           JOIN v_GS_SYSTEM_CONSOLE_USER
             ON v_GS_SYSTEM_CONSOLE_USER.ResourceID = v_R_System.ResourceID
         GROUP 
             BY Name0 )

second, your syntax is wrong, you cannot have the WHERE clause mixed in amongst the joins (and the OR condition in the WHERE clause is superfluous)

also, the last subquery needs a table alias

Thanks a lot for your reply and tips. Yes as you guessed i’m very new to sql queries, used to get away with simple ones but not anymore :slight_smile:
I’ll give it a go tomorrow and see how I get on, it should be enough to point me on the right path.

reps to this dude\dudette

sorry sent to quickly where would I alias the 2nd table?

your query makes my head/eyes hurt! lol.

My sql too once looked like yours, but then I met this guy named Rudy. He and his book showed me the light. :wink:

right after the parentheses…

INNER
  JOIN ( SELECT ... ) [COLOR="Red"]AS x[/COLOR]

Again, thanks fo rthe replies guys…

I assume it’s Rudy’s guide to simple SQl query you are referring to :wink: will check it out.