Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Help with SQL Join Query

Posted on 2011-03-24
2
Medium Priority
?
264 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 41

Accepted Solution

by:
Sharath earned 1000 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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

730 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