Link to home
Start Free TrialLog in
Avatar of grizzjeeper
grizzjeeperFlag for United States of America

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of Sharath S
Sharath S
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of grizzjeeper

ASKER

Thank you!  exactly what i was looking for.