SELECT'ing a master record and all its related foreign detail records, in a single query?

Question

I have a table that could have thousands (millions maybe?) of records. It is basically an audit trail table that stores special log entries. It's called "Logs".

There is also a related table called "LogsExtended" which stores zero or many additional records for each entry in the Logs table.

There is a foreign key relationship setup, complete with deletion cascade etc.

I am doing a SELECT on the Logs table to select all records that occurred in a specific time range, say "the last 30 days".

However I want to somehow simultaneously select the related foreign records in the LogsExtended table. With the intention that the results of this query will be filled into a DataSet that has the proper DataRelation setup.

I have tried using various JOIN clauses but these all tend to result in the wrong behaviour -- where entries from Logs get repeated for each related record there is in the LogsExtended table.

I really want to avoid the obvious fallback solution which is to query the Logs table first, then, for each result, run an additional query to get the LogsExtended records as well. That strikes me as pretty wasteful and could result in thousands of queries being run.

I think I'm making a bit of a mountain of a molehill out of this but I just can't figure it out.

Thanks.

Answer 1

The essence of join in a one-to-many relationship is to multiply the rows in the "one" table as many times as the "many" table. One option you could go with is take up only the LogID from the Logs table, together with all the rest of the information from the LogsExtended table:

SELECT L.ID, E.*
FROM Logs L, LogsExtended E
WHERE 
L.ID = E.ID
AND
(Some date limitation on the Logs table)

Answer 2

You need to use a left join, otherwise it will not show records from Logs that have no items int LogExtended. As so

DECLARE @StartDate DATETIME,
     @EndDate DATETIME

SELECT  @StartDate = '01 Jun 2009',
     @EndDate = '30 Jun 2009'

SELECT  *
FROM    Logs l LEFT JOIN
     LogsExtended le ON l.LogID = le.LogID
WHERE   l.Date BEYWEEN @StartDate AND @EndDate

if you want 2 result set you need to execute the query as

DECLARE @StartDate DATETIME,
     @EndDate DATETIME

SELECT  @StartDate = '01 Jun 2009',
     @EndDate = '30 Jun 2009'

SELECT  l.*
FROM    Logs l 
WHERE   l.Date BEYWEEN @StartDate AND @EndDate

SELECT  le.*
FROM    Logs l INNER JOIN
     LogsExtended le ON l.LogID = le.LogID
WHERE   l.Date BEYWEEN @StartDate AND @EndDate

this will return all logs between dates, then all extended logs for that same original logs set