Unwanted results with Select stmt

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Unwanted results with Select stmt

Author

Topic

1sqlover
Yak Posting Veteran

56 Posts

Posted - 2006-05-04 : 15:10:25

I am creating a view that I want to display the Event Log Failed Logon Information (see SQL Statement below)

Table EL_529 = Event Logs of failed logons
Table EL_CompInfo = Misc info regarding the PC, whose Event Logs are in question (i.e. PC Owner, and PC Description)

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\**Table EL_UserInfo = Info regarding to the users in my domain (i.e. UserName, Display Name etc)

///////////////////////////////////////////////////////////////////////////////////////////////

**My only problem is, I want to be able to include usernames that are not in the EL_UserInfo table.

For example if I have two (2) users in my domain: John and Mary.
And I have three usernames in the Event Logs for failed logon attempts: John, Mary & Adam.

I want Adam to show up in the view along with John and Mary and not be excluded, presently Adam is being excluded.

Is this possible?

==============================================================
SELECT
T1.EventID, T1.TimeWritten, T1.TimeGenerated, T1.UserName, T1.[Domain],
T1.WorkStation, T1.Type, T1.OrigMsg, T1.ComputerName,
T2.ComputerName AS CompName, T2.CompOwner, T3.UserName AS UsrName, T3.UserNamewDN, T3.UserDisplayName
FROM EL_529 T1, EL_CompInfo T2, EL_UserInfo T3
WHERE T1.ComputerName = T2.ComputerName
==============================================================

Thanks (I hope this makes sense)
Sorry, I also posted this in the "New to SQL Section"

epoh

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2006-05-04 : 15:24:51

If you do a typical JOIN between Table1 and Table2 it will only pull where there is a match. That would be called an INNER JOIN, or as a friend calls it an INTERSECTION join, because it will only pull rows if there is an intersection.

If you don't put any criteria to join the table that is called "overflowing with data" because it will pull every combination as you probably found with the above since you have no criteria at all for joining to T3 so it probably pulled every user for every row of the others.

To get what you are looking for that would be called an OUTER JOIN. In your case if you want to JOIN to T3 but wan't all of the data from the other tables, but the "extra" data contained in T3 if their is an intersection you'll need to specifically define that.

Select Table1.Field1, Table2.Field10
FROM Table1 INNER JOIN Table2 on Table2.Field3 = Table1.Field3

Will yield all of the rows from Table1 that do have a match in Table2

While the following will yield all of the rows from Table1 and if there is no match on Field3 for Table2 for that row, it will output a NULL value for Field10.

Select Table1.Field1, Table2.Field10
FROM Table1 LEFT OUTER JOIN Table2 on Table2.Field3 = Table1.Field3

Not knowing your table structure's or what you would need to join on you would do something like this:
T1.EventID, T1.TimeWritten, T1.TimeGenerated, T1.UserName, T1.[Domain],
T1.WorkStation, T1.Type, T1.OrigMsg, T1.ComputerName,
T2.ComputerName AS CompName, T2.CompOwner, T3.UserName AS UsrName, T3.UserNamewDN, T3.UserDisplayName
FROM EL_529 T1 INNER JOIN EL_CompInfo T2 ON T1.ComputerName = T2.ComputerName
LEFT OUTER JOIN EL_UserInfo T3 on T3.FieldThatMightMap = T1 or T2.FieldThatMightMap

Hope it helps,
Dalton

Blessings aren't so much a matter of "if they come" but "are you noticing them."

1sqlover
Yak Posting Veteran

56 Posts

Posted - 2006-05-04 : 15:35:10

Thanks alot that worked!!!!!

epoh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources