INNER JOIN query consolidation

[Geates]

I have two "main" EAVs that INNER JOIN with common value tables. I query them separately and use UNION to join the two recordsets. However, this produces duplicates and theoretically reduces performance because of repetative table loads. I'd like to merge the two queries into one but am having difficultly with the logic.

These tables have 100,000s of rows

Audits_Assets:
pk | Host | Entity | Attrib | Value | TimeStamp
-----------------------------------------------
1 1 2 3 1 1
...

Audits_Logons:
pk | Host | Entity | Attrib | Value | TimeStamp
-----------------------------------------------
1 1 4 5 6 2
...

Audits_Values:
pk | Value
---------------
1 b-136615
2 Computer
3 HostName
4 Logon
5 CurrentUser
6 Geates
...

Audits_TimeStamps:
pk | Value
---------------
1 2/15/2011 3:16:00 PM
2 2/16/2011 9:30:00 AM
...

SELECT DISTINCT(tblLogons.Host) AS Host
       tblTimeStamps.Value AS TimeStamp
FROM   Audits_Logons AS tblLogons INNER JOIN
       Audits_TimeStamps AS tblTimeStamps ON tblLogons.TimeStamp = tblTimeStamps.pk INNER JOIN
       Audits_Values AS tblValues ON tblLogons.Value = tblValues.pk
WHERE  (tblValues.Value LIKE '%something%')
GROUP BY tblLogons.Host

UNION

SELECT DISTINCT(tblAssets.Host) AS Host
       tblTimeStamps.Value AS TimeStamp
FROM   Audits_Assets AS tblAssets INNER JOIN
       Audits_TimeStamps AS tblTimeStamps ON tblAssets.TimeStamp = tblTimeStamps.pk INNER JOIN
       Audits_Values AS tblValues ON tblAssets.Value = tblValues.pk
WHERE  (tblValues.Value LIKE '%something%')
GROUP BY tblAssets.Host

Based on this extremely crude representation of actuality, is there a way to consolidate these queries into one?

-Geates

NOTE: I'm programmer new to this particular forum and have only observatory knowledge with MSSQL. I appologize for any "questionables"

"Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live."
- Martin Golding

 

[r937]

several comments...

first, DISTINCT is ~not~ a function!

second, if you use GROUP BY tblAssets.Host, then you will get only one row for each host, so the DISTINCT is unnecessary

third, UNION removes duplicates (UNION ALL retains them), so if the two SELECTs in your UNION query happen to return the same timestamp value for a given host, then one of those will be be removed by the UNION

i'm not sure what your actual question was any more...

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Geates]

You may have inadvertantly suggested an alternative method by pointing out the flaws. Like I said, I have limited knowledge of mssql and it's syntax so I appreciate your comments.

You're right, UNION eliminated duplicates. I assumed my results contained "duplicates" without looking at the other data returned. As the query suggest, a UNION takes place between to recordsets both containing a Host and a MAX TimeStamp. I now see that the issue is not with perceived duplicate entries but instead with which result from either EAV should be returned.

Example:

RecordSet from Audits_Assets

pk | Host | TimeStamp
---------------------
1 A 2/16/2011 12:00 PM
2 B 2/16/2011 12:10 PM
3 C 2/16/2011 12:20 PM

RecordSet from Audits_Logons

pk | Host | TimeStamp
---------------------
1 A 2/16/2011 12:00 PM
2 B 2/16/2011 12:15 PM
2 D 2/16/2011 12:30 PM

UNIONized RecordSet

pk | Host | TimeStamp
---------------------
1 A 2/16/2011 12:00 PM
2 B 2/16/2011 12:10 PM
3 B 2/16/2011 12:15 PM <-- perceived duplicate
4 C 2/16/2011 12:20 PM
5 D 2/16/2011 12:30 PM

Notice that the unionized recordset contains only one entry for host A, C, and D. Obviously, this is because the information for these hosts in the original recordsets are identical. There are two entries for host B because the information from the individual recordsets are not identical. I'd like my unionized recorset to include ONLY the most recent information.

Desired UNIONized RecordSet

pk | Host | TimeStamp
---------------------
1 A 2/16/2011 12:00 PM
2 B 2/16/2011 12:15 PM
3 C 2/16/2011 12:20 PM
4 D 2/16/2011 12:30 PM

-Geates


"Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live."
- Martin Golding

 

[r937]

in your original query, your SELECTs return only two columns, host and timestamp, but your sample data also shows a pk

may i assume the pk is not important?

SELECT Host
     , MAX(TimeStamp) AS max_TimeStamp
  FROM ( SELECT tblLogons.Host
              , tblTimeStamps.Value AS TimeStamp
           FROM Audits_Logons AS tblLogons 
         INNER 
           JOIN Audits_TimeStamps AS tblTimeStamps 
             ON tblTimeStamps.pk = tblLogons.TimeStamp
         INNER 
           JOIN Audits_Values AS tblValues 
             ON tblValues.pk = tblLogons.Value
          WHERE tblValues.Value LIKE '%something%'
         UNION 
         SELECT tblAssets.Host
              , tblTimeStamps.Value AS TimeStamp
           FROM Audits_Assets AS tblAssets 
         INNER 
           JOIN Audits_TimeStamps AS tblTimeStamps 
             ON tblTimeStamps.pk = tblAssets.TimeStamp
         INNER 
           JOIN Audits_Values AS tblValues 
             ON tblValues.pk = tblAssets.Value
          WHERE tblValues.Value LIKE '%something%'
       ) AS u

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Geates]

You assume correctly. I thought it might be as simple as encapsulating the UNION with a SELECT. Thanks for you input

-Geates

"Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live."
- Martin Golding