How To Get The Latest Information...

Hi,

I have the following situation: For a list of contacts I need to return all the contacts with the latest campaign that he is associated...
Here is my tables..




Code Snippet
CREATE TABLE [MAILING_TAB-Contatos] (
[CODContato] [int] NOT NULL ,
[CNPJ] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[CPF] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[RazaoSocial] [varchar] (55) COLLATE Latin1_General_CI_AS NULL ,
[Endereco] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Cidade] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Bairro] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Estado] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[CEP] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Telefone] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Telefone2] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Telefone3] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[FAX] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Responsavel] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[ResponsavelTelefonia] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[Celular] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Email] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[Site] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[IndicadoPor] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Notas] [text] COLLATE Latin1_General_CI_AS NULL ,
[Interesses] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[CODMail] [int] NULL ,
[CODRamoAtividade] [int] NULL ,
[CODTipoAtividade] [int] NULL ,
[Porte] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
CONSTRAINT [PK_MAILING_Main-Contacts] PRIMARY KEY CLUSTERED
(
[CODContato]
) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [MAILING_TAB-ClienteCampanha] (
[GerenteVendas] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[CODContato] [int] NOT NULL ,
[CODCampanha] [int] NOT NULL ,
[DATAAtribuicao] [datetime] NULL CONSTRAINT [DF_MAILING_TAB-ClienteCampanha_DATAAtribuicao] DEFAULT (getdate()),
CONSTRAINT [PK_MAILING_ClienteCampanha] PRIMARY KEY CLUSTERED
(
[CODContato],
[CODCampanha]
) ON [PRIMARY] ,
CONSTRAINT [FK_MAILING_ClienteCampanha_MAILING_Campanhas] FOREIGN KEY
(
[CODCampanha]
) REFERENCES [MAILING_TAB-Campanhas] (
[CODCampanha]
) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT [FK_MAILING_ClienteCampanha_MAILING_Main-Contacts] FOREIGN KEY
(
[CODContato]
) REFERENCES [MAILING_TAB-Contatos] (
[CODContato]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]

CREATE TABLE [MAILING_TAB-Campanhas] (
[CODCampanha] [int] IDENTITY (1, 1) NOT NULL ,
[DescricaoCampanha] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
CONSTRAINT [PK_MAILING_Campanhas] PRIMARY KEY CLUSTERED
(
[CODCampanha]
) ON [PRIMARY]
) ON [PRIMARY]


And sample data
-----Contatos----
16228 61349031000147 91055652949 *** ATLETICA FLORESTA R PRIMITIVA VIANCO 405 OSASCO CENTRO SP 06010000 1136818676 1136826872 NULL NULL TATIANA DA SILVA NULL 1136853252 NULL NULL NULL NULL NULL 700 NULL NULL NULL

16229 60504586000153 00444817808 COOPERATIVA MISTA TRAB DA GDE S PAULO LTDA R ANTONIO AGU 751 OSASCO CENTRO SP 06013000 1136543715 1136816656 NULL NULL EVANDECI JORGE CERQ OLIVEIRA NULL 1136816743 NULL NULL NULL NULL NULL 700 NULL NULL NULL

16230 00124567000170 36903809520 CALCADOS GABRIELLA OSASCO LTDA R ANTONIO AGU 115 OSASCO CENTRO SP 06013006 1136996011 1136996018 NULL NULL GILSON BASTOS DOS SANTOS NULL 1136996305 NULL NULL NULL NULL NULL 700 NULL NULL NULL

16231 00182385000155 07929808831 PRYMUS BEGNINI COM DE CALCADOS E CONFECCOES LTDA R ANTONIO AGU 177 OSASCO CENTRO SP 06013006 1136995410 NULL NULL NULL CLAUDIO DE OLIVEIRA COSTA NULL NULL NULL NULL NULL NULL NULL 700 NULL NULL NULL

16232 53679098000111 00368397874 ANGELA MODAS LTDA R ANTONIO AGU 293 OSASCO CENTRO SP 06013006 1136854672 NULL NULL NULL JORGE MIGUEL DA SILVA NULL NULL NULL NULL NULL NULL NULL 700 NULL NULL NULL


---Campanhas----
1 Teste
2 teste2
3 teste3
4 teste4

--- ClienteCampanha ---
1 16228 1 2008-02-28 00:00:00.000
1 16228 2 2008-02-26 00:00:00.000
2 16229 1 2008-02-28 00:00:00.000
4 16230 3 2008-02-25 00:00:00.000








--The result for my query must be contatcs 16228, 16229, 16230, for campaigns 1, 1, 3
I need to see all fields from contatcs, the name of the campaign, and it's date...

It's like if the campaign number 2 has been disabled or expired
Thanks