By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,945 Members | 1,540 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,945 IT Pros & Developers. It's quick & easy.

Complex MS SQL Query help

P: 2
Hi,

I am new to queries, and I may be making this more difficult than necessary, but here is what I am trying to do.

I am trying to query a DB, and returen information from several tables as follows:
I have been able to get the query to work so far, but now I need to go a little deeper, adn I am stumped.

so far I have the following.
Tables = 3 (need to query 4 I think) T1, T2, T3

SELECT DISTINCT

T1.CLIENT_ID
T1.SET_NAME
T1.EVENT_ID
T2.ERROR
T3.MACHINE_NAME
T2.TEXT

FROM

T1, T2, T3

WHERE

T1.CLIENT_ID = T3.CLIENT_ID AND
T1.EV_TIME BETWEEN 'DATE' AND 'DATE' AND
T1.EVENT_ID = T2.SESSION_ID

okay... this query gives me the info I need (essentially it show me all the errors for a given client on a given day.

Now I have another table called event_Filter that allow me to set a severity (0 = Normal, 1 = Intermediate, 2 = Critical) for the different Error numbers (ERROR is a field in T2, and "may" correspond to the ErrNO filed in the event_Filter table)

I want to be able to see only the results where the Error is either a 1 OR a 2 in the Event_Filter table.

The hard part is that the Error may not be in this filter. If the error is not in the Filter, then I want to view it as well.

Essentiall what I am trying to do is be able to view all the errors that are either critical or intermediate or unknown.

Currently my query above will show me all errors, now I need to narrow this down, and I am unsure how to do this.

Thanks,

Mike
Feb 4 '08 #1
Share this Question
Share on Google+
4 Replies


code green
Expert 100+
P: 1,726
Expand|Select|Wrap|Line Numbers
  1. want to be able to see only the results where the Error is either a 1 OR a 2 in the Event_Filter table.
Then you need someting like
Expand|Select|Wrap|Line Numbers
  1. JOIN Event_Filter ON (T1.EVENT_ID = 1 OR T1.EVENT_ID = 2)
The brackets are critical
The hard part is that the Error may not be in this filter. If the error is not in the Filter, then I want to view it as well.
To pull out records regardless of being in a joining table then use a LEFT or RIGHT JOIN.
Expand|Select|Wrap|Line Numbers
  1. LEFT JOIN Event_Filter ON (T1.EVENT_ID = 1 OR T1.EVENT_ID = 2)
Feb 4 '08 #2

P: 2
okay,

Not sure that this will work. I may need to explain a little better.

I am trying to generate a report/query that will show me all of the errors on a per client per set level for the data range.

the table are ordered as follows
T1 (List of all events for all clients both normal and non-normal events)
Fields
Client_ID Event_ID SET_ID SET_NAME EV_TIME

T2 (Information about events from table1)
Fields
Client_ID Session_ID Time_Stamp Event_Type Category Error Text

T3 (List of Clients)
Fields
Client_ID Machine_Name

T4 (List of errors that have been modified per client)
Fields
Client_ID Importance ErrNO Filter Category

T5 (list of all possible errors)
Fields
Err_NO Err_Text Category Type Priority Activity

Now...
Client_ID corresponds on all the tables.
T2.Session_ID is the same as T1.Event ID

T2 is the table that contains the majority of the info that I need to match.

I want to report the Client_ID, Machine_Name, SET_NAME if
the T2.Error is the same as T4.ERR_NO AND T4.Filter <> 0 (0= normal) OR
T2.Error is the same as T5.ERR_NO


Hope this helps.
Feb 4 '08 #3

code green
Expert 100+
P: 1,726
Not sure that this will work
Have you tried it?
OK. I might be on the wrong track, AND I have never tried double brackets but this should get you closer
Expand|Select|Wrap|Line Numbers
  1. SELECT Client_ID, Machine_Name, SET_NAME FROM T2
  2. JOIN T4 ON ((T2.Error = T4.ERR_NO AND T4.Filter <> 0)
  3. OR (T2.Error = T5.ERR_NO))
Feb 5 '08 #4

amitpatel66
Expert 100+
P: 2,367
Try this:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT DISTINCT
  3. T1.CLIENT_ID
  4. T1.SET_NAME
  5. T1.EVENT_ID
  6. T2.ERROR
  7. T3.MACHINE_NAME
  8. T2.TEXT
  9. FROM
  10. T1, T2, T3
  11. WHERE
  12. T1.CLIENT_ID = T3.CLIENT_ID AND
  13. T1.EV_TIME BETWEEN 'DATE' AND 'DATE' AND
  14. T1.EVENT_ID = T2.SESSION_ID AND
  15. T2.ERROR IN (1,2)
  16.  
Will this query not give you what you require?
As ou said, you need to list down the error even if it is not there in event_filter table, then you need not refer to event_filter table and just by adding one more condition as shown in above query, it should fetch you what you want!!
Feb 5 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.