grizzjeeper
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER