Solved

Help with SQL Join Query

Posted on 2011-03-24
2
256 Views
Last Modified: 2012-05-11
I'm trying to work with 4 different tables, 3 tables contain alarm information and the other contains clients information.  What i need to do is query the client table to find out which clients have reporting on.  From there i need to query each of the other tables (backup, antivirus, and server) and pull a max alarm level within lets say the past 24 hours.  Here is what I have so far. This works except does not have the 24 hour date range.  If I add the date range in, some clients disappear, which i dont want.  I need the clients to still appear even if they have null values.

The clients table has two columns im working with:
clientid ->the clients name
reportingon -> either true or false

The other 3 tables have the following columns
clientid ->the clients name
alarmlevel -> 1-4 depending on severity - 4 being worse
alarmdate -> The date/time the alarm went off


SELECT     a.Name, MAX(b.alarmlevel) AS BULVL, MAX(c.alarmlevel) AS AVLVL, MAX(d.alarmlevel) AS SVLVL FROM Clients AS a
          LEFT OUTER JOIN Backups AS b ON a.clientid= b.clientid
          LEFT OUTER JOIN Antivirus AS c ON a.clientid= c.clientid
          LEFT OUTER JOIN Server AS d ON a.clientid = d.clientid
WHERE     (a.reportingon = 'True')
GROUP BY a.clientid
ORDER BY a.clientid
0
Comment
Question by:grizzjeeper
2 Comments
 
LVL 40

Accepted Solution

by:
Sharath earned 250 total points
ID: 35210258
try like this.
SELECT     a.Name, MAX(b.alarmlevel) AS BULVL, MAX(c.alarmlevel) AS AVLVL, MAX(d.alarmlevel) AS SVLVL FROM Clients AS a
          LEFT OUTER JOIN Backups AS b ON a.clientid= b.clientid and b.Datefield > dateadd(hour,-24,getdate())
          LEFT OUTER JOIN Antivirus AS c ON a.clientid= c.clientid and c.Datefield > dateadd(hour,-24,getdate())
          LEFT OUTER JOIN Server AS d ON a.clientid = d.clientid and d.Datefield > dateadd(hour,-24,getdate())
WHERE     (a.reportingon = 'True')
GROUP BY a.clientid 
ORDER BY a.clientid

Open in new window

0
 

Author Closing Comment

by:grizzjeeper
ID: 35210433
Thank you!  exactly what i was looking for.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.‚Äč
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now