retrieving selected join records

Hi,
I have the folowing 3 (SS2005) tables:

CREATE TABLE [dbo].[tblSubscription](
[SubscriptionID] [int] IDENTITY(1000000,1) NOT NULL,
[SubscriberID] [int] NOT NULL,
[Status] [int] NOT NULL,
[JournalID] [int] NOT NULL,

CREATE TABLE [dbo].[tblTransaction](
[TransactionID] [bigint] IDENTITY(100000000,1) NOT NULL,
[TransactionTypeID] [int] NOT NULL,
[SubscriptionID] [int] NOT NULL,
[Created] [datetime] NOT NULL,

CREATE TABLE [dbo].[tblMailing](
[MialingID] [bigint] IDENTITY(1000000000,1) NOT NULL,
[SubscriptionID] [int] NOT NULL,
[MailTypeID] [int] NOT NULL,
[MailDate] [datetime] NOT NULL

So for each subscription there can be 1 or more transactions and 0 or
more mailings, and the mailings are not necassarily related to the
transactions. What I am having difficulty doing is this:

I wish to select tblMailing.MailingID, tblMailing.MailDate,
tblMailing.SubscriptionID (or tblSubscription.SubscriptionID),
tblSubscription.SubscriberID, tblSubscription.Status,
tblTransaction.TransactionID, tblTransaction.Created, but I only wish
to retrieve rows from the transaction table where
tblTransaction.Created is the latest dated transaction for that
subscription.
I.E. (maybe this makes more sense..:) I wish to select all rows from
tblMailing along with each mailing's relevent subscription details,
including details of the LATEST TRANSACTION for each of those
subscriptions.

I am currently working along the lines of MAX(tblTransaction.Created)
and possibly GROUP BY in a subquery, but cannot quite figure out the
logic.

Any help appreciated.

Thanks, KoG

King:

Are you wanting the subscription record to appear in the report even if there are as of yet no mailings? That is, do I need to use an outer join or an inner join? I am for the moment assuming that you want the inner join.

Dave

|||

set nocount on
declare @.tblSubscription table
( subscriptionID integer not null,
subscriberID integer not null,
status integer not null,
journalID integer not null,
primary key (subscriptionID)
)

declare @.tblTransaction table
( transactionID integer not null,
transactionTypeId integer not null,
subscriptionID integer not null,
created datetime not null
primary key (transactionID),
unique (subscriptionID, transactionID)
)

declare @.tblMailing table
( mailingId bigint not null,
subscriptionID integer not null,
mailTypeId integer not null,
mailDate datetime not null,
primary key (mailingID),
unique (subscriptionId, mailingId)
)

insert into @.tblSubscription values (1000001, 1000001, 1, 1)
insert into @.tblSubscription values (1000002, 1000002, 1, 1)
insert into @.tblSubscription values (1000003, 1000001, 2, 1)
--select * from @.tblSubscription

insert into @.tblTransaction values (1000001, 1, 1000001, '3/15/6' )
insert into @.tblTransaction values (1000002, 2, 1000001, '4/7/6' )
insert into @.tblTransaction values (1000003, 1, 1000002, '4/3/6' )
insert into @.tblTransaction values (1000004, 1, 1000003, '5/8/6' )
insert into @.tblTransaction values (1000005, 2, 1000003, '10/14/6')
insert into @.tblTransaction values (1000006, 4, 1000003, '9/1/6' )
--select * from @.tblTransaction

insert into @.tblMailing values (1000001, 1000001, 1, '3/15/6' )
insert into @.tblMailing values (1000002, 1000001, 2, '4/4/6' )
insert into @.tblMailing values (1000003, 1000003, 1, '5/9/6' )
insert into @.tblMailing values (1000004, 1000003, 3, '9/3/6' )
--select * from @.tblMailing

--set statistics io on
select m.mailingId,
m.MailDate,
s.subscriptionId,
s.subscriberId,
s.Status,
t.TransactionId,
t.created
from @.tblSubscription s
inner join @.tblMailing m
on s.subscriptionId = m.subscriptionId
inner join
( select q.subscriptionId,
q.transactionId,
row_number () over
( partition by q.subscriptionId
order by q.created desc, q.transactionId desc
) as Seq,
created
from @.tblTransaction q
) t
on t.subscriptionId = s.subscriptionId
and seq = 1
--set statistics io off

-- -- Sample Output: -

-- mailingId MailDate subscriptionId subscriberId Status TransactionId created
-- -- -- -- -- - --
-- 1000001 2006-03-15 00:00:00.000 1000001 1000001 1 1000002 2006-04-07 00:00:00.000
-- 1000002 2006-04-04 00:00:00.000 1000001 1000001 1 1000002 2006-04-07 00:00:00.000
-- 1000003 2006-05-09 00:00:00.000 1000003 1000001 2 1000005 2006-10-14 00:00:00.000
-- 1000004 2006-09-03 00:00:00.000 1000003 1000001 2 1000005 2006-10-14 00:00:00.000

|||Hi Dave,

I only wish to select subscription rows where there is a mailing associated with the subscription. In fact, the driver of the query should be the mailings table, so for each row in tblMailing get the relevent subscription (& latest transaction) data. That means there may be several rows where the data in the subscription-related columns (& hence transaction related ones too) are the same, as a subscription may have several mailings.

I assume that means the inner join is required..

Thanks, Nick
sql