?
Solved

find unique value from a three way join

Posted on 2008-10-29
5
Medium Priority
?
530 Views
Last Modified: 2012-05-05
Trying to capture processes taking io and need to select distiinct spid from the query.  Was trying to select it into a temp table, but not allowed due to 'NULL' values.  Using distinct also isn't helping.  I must be doing my join incorrectly but unsure how to proceed... it has to be something elementary.

     select convert (char(19), getdate()),
                convert (char(15), b.name) as login,
                convert (char(15), c.name) as dbname,
                convert  (char(4), a.spid) as spid,
                                             a.status,
                                             acmd,
                                             a.physical_io,
               convert  (char(5), a.cpu) as cpu,
               convert  (char(5), a.blocked) as block,
               convert  (char(5), a.time_blocked) as time

    from master..sysprocesses   a,
            master..syslogins           b,
            master..sysdatabases   c

    where physical_io > 0
0
Comment
Question by:sanate
  • 2
  • 2
5 Comments
 
LVL 6

Expert Comment

by:RPCIT
ID: 22833169
The SQL you wrote has some basic problems that I see, for instance you don't have any relations defined, but this could just be because I use MSSQL 2000 and 2005.  

But regardless, you should be able to wrap the whole thing (assuming your happy with the results) with
   SELECT DISTINCT spid FROM ( YOUR_SQL_HERE ) subQueryAlias

hope this helps.
SELECT DISTINCT spid FROM (
	       select convert (char(19) , getdate() )AS _now,
                convert (char(15), b.name) as login,
                convert (char(15), c.name) as dbname,
                convert  (char(4), a.spid) as spid,
                                             a.status,
                                             a.cmd,
                                             a.physical_io,
               convert  (char(5), a.cpu) as cpu,
               convert  (char(5), a.blocked) as block--,
               --convert  (char(5), a.time_blocked) as time
 
    from master..sysprocesses   a,
            master..syslogins           b,
            master..sysdatabases   c
 
    where physical_io > 0) sub

Open in new window

0
 

Author Comment

by:sanate
ID: 22833391
Thanks for looking into this.  But we've been having some issues tracking logins giving the databases angst.  This solution does indeed work for isolating unique spids... but also need to capture the other columns in the select statement.  By the unique statement I'm getting a row returned for every database... so its returning in this case 15 rows for every spid.
0
 
LVL 6

Expert Comment

by:RPCIT
ID: 22833711
you just need to add the fields that you want to see, and then some aggregate function for the others.  If you want all the fields, then distict will only work where all the fields are identical.

SELECT DISTICT spid, MAX(Login) as Login FROM (.....) alias GROUP BY spid

would do a rollup, and only give you spids, but you would lost detail on the login field.
0
 
LVL 19

Accepted Solution

by:
grant300 earned 500 total points
ID: 22833896
The problem is you are not specifying the join SARGs in the WHERE clause so you are getting a Cartesian product; A x B x C rows total.  You have to join the suid in the sysprocesses table to the suid in syslogins and the dbid in sysprocesses to the dbid in sysdatabases.

I personally prefer to use the ANSI SQL syntax for joins as they are less ambiguous and easier to read.  See the snippet.

Regards,
Bill

  select convert (char(19), getdate()),
         convert (char(15), b.name) as login,
         convert (char(15), c.name) as dbname,
         convert  (char(4), a.spid) as spid,
         a.status,
         a.cmd,
         a.physical_io,
         convert  (char(5), a.cpu) as cpu,
         convert  (char(5), a.blocked) as block,
         convert  (char(5), a.time_blocked) as time
    from master..sysprocesses   a
    join master..syslogins      b
      on b.suid = a.suid
    join master..sysdatabases   c
      on c.dbid = a.dbid
   where physical_io > 0

Open in new window

0
 

Author Closing Comment

by:sanate
ID: 31511270
Thanks much, don't know why I was drawing a blank on that.  Must have wasted half of yesterday on it.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I am posting this in case anyone runs into similar issues that I did, this may save you a lot of grief: Condition: 1. Your NetBIOS domain name contains an ampersand " & " character.  (e.g. AT&T) 2. You've tried to run any Microsoft installation…
Whether you have a site with just static html pages or a dynamic database-driven one, this step-by-step migration guide will help you get started with your new DV server. This guide is by no means comprehensive but it should cover the basics to get …
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses
Course of the Month9 days, 2 hours left to enroll

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question