No duplicates in query Confusion 1

[Buzzmeister14]

I have multiple tables in an SQL 2k DB

The problem lies in creating a view for this database that only contains required information, making it easier to create a report at a later date

There are three tables in question Client_Table, Policy_Table, Trade_LU

Client Table has a primary key of ClientId which is foreign in Policy_Table
Trade_LU has TradeId as Primary which is foreign in Client

I run the following SQL query

SELECT
Client."ClientId",
Client."Title",
Client."Forename",
Client."Surname",
Client."Company",
Client."Address1",
Client."Address2",
Client."Address3",
Client."Town",
Client."County",
Client."Postcode",
Client."Phone",
Client."Fax",
Client."Business_Description_VC",
Policy."Status",
Policy."EffectiveDate",
Trade_LU."Trade"
FROM
(ldu.dbo.Client Client INNER JOIN ldu.dbo.Policy Policy ON
Client."ClientId" = Policy."ClientId")
INNER JOIN ldu.dbo.Trade_LU Trade_LU ON
Client."TradeId" = Trade_LU."TradeId"
ORDER BY
Client."ClientId" ASC

However there are duplicate lines for client returned, because there are many policies for individual clients


However I would like no duplicates reported – so there would be one line for each Client – Have tried various distinct queries etc with no avail

Any help greatly appreciated

 

[JamesLean]

If their are multiple policies per client, which one do you want to display?

--James

 

[Buzzmeister14]

Thanks for replying James

I don't want to display the policy information per say

I just want to create a list of clients that have archived policies so that I can export a list of archived clients

So the policy information is secondary I just want CLient details name, address etc for all policies that are status archive

the extra fields included in the original query were there so i could trace any problems a bit better - that backed fired!!

 

[JamesLean]

In that case, get rid of the fields from policy:

SELECT [b]DISTINCT[/b]
Client."ClientId",
Client."Title",
Client."Forename",
Client."Surname",
Client."Company",
Client."Address1",
Client."Address2",
Client."Address3",
Client."Town",
Client."County",
Client."Postcode",
Client."Phone",
Client."Fax",
Client."Business_Description_VC",
Trade_LU."Trade"
FROM
(ldu.dbo.Client Client INNER JOIN ldu.dbo.Policy Policy ON
Client."ClientId" = Policy."ClientId")
INNER JOIN ldu.dbo.Trade_LU Trade_LU ON
Client."TradeId" = Trade_LU."TradeId"
ORDER BY
Client."ClientId" ASC

--James

 

[Buzzmeister14]

Sorry James

piece of info left out of original thread

The code you gave worked fine

But I would like to report on clients

so the selection for clietns would be based on weather the policy status was archived

something like this appended to the statement
WHERE Policy.Status LIKE 'Archive'

Wasn't very clear in my first question - apologies

Effectively what I'm doing is searching through a db of policies looking for a status of archive - I then want to return the client name address etc for each of those policies - but not duplicates of those clients

Hope this helps - thanks for the time taken

 

[JamesLean]

OK, try this:

SELECT
Client."ClientId",
Client."Title",
Client."Forename",
Client."Surname",
Client."Company",
Client."Address1",
Client."Address2",
Client."Address3",
Client."Town",
Client."County",
Client."Postcode",
Client."Phone",
Client."Fax",
Client."Business_Description_VC",
Trade_LU."Trade"
FROM ldu.dbo.Client Client INNER JOIN ldu.dbo.Trade_LU Trade_LU ON Client."TradeId" = Trade_LU."TradeId"
WHERE Client.ClientID IN (SELECT ClientID FROM ldu.dbo.Policy WHERE status = 'archived')
ORDER BY Client."ClientId" ASC

--James

 

[Buzzmeister14]

Thanks James

Is there any way I can also pull the Effective Date for that policy into the report as well

ANy help much appreciated

 

[JamesLean]

Yes, but my question would be the same - if their are several archived policies for a client, which one would you want to return?

eg, it would be something like this if you wanted to return the latest date:

SELECT
Client."ClientId",
Client."Title",
Client."Forename",
Client."Surname",
Client."Company",
Client."Address1",
Client."Address2",
Client."Address3",
Client."Town",
Client."County",
Client."Postcode",
Client."Phone",
Client."Fax",
Client."Business_Description_VC",
Trade_LU."Trade",
archive.effectivedate
FROM ldu.dbo.Client Client INNER JOIN ldu.dbo.Trade_LU Trade_LU ON Client."TradeId" = Trade_LU."TradeId"
  INNER JOIN (SELECT ClientID, MAX(effectivedate) AS effectivedate FROM ldu.dbo.Policy WHERE status = 'archived' GROUP BY clientid) archive ON client.clientid = archive.clientid
ORDER BY Client."ClientId" ASC

--James

 

[Buzzmeister14]

Thanks - James

Excellent work - It didn't matter which date that was pulled after - they were all the same anyway so your code worked perfectly


Thanks for the time and effort

Much appreciated