• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 267
  • Last Modified:

Help with SQL Join Query

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
grizzjeeper
Asked:
grizzjeeper
1 Solution
 
SharathData EngineerCommented:
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
 
grizzjeeperAuthor Commented:
Thank you!  exactly what i was looking for.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now