SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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)

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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 --
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up

    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
    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

  4. #4
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sorry sent to quickly where would I alias the 2nd table?

  5. #5
    SitePoint Evangelist
    Join Date
    Sep 2006
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by skylizard View Post
    where would I alias the 2nd table?
    right after the parentheses...
    Code:
    INNER
      JOIN ( SELECT ... ) AS x
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Again, thanks fo rthe replies guys...

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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •