asked on

Query returns duplicates

I have the following query that returns duplicates, I know If I add Distinct it wont.
Is there any other way to do this, this started to happen when I joind 2 more tables to my Query:

SELECT *
FROM AOS_PROVIDER_CONTRACT_EZCAP AS A
INNER JOIN AOS_PROVIDER_CLASSCODE_REF ON
A.CLASS = AOS_PROVIDER_CLASSCODE_REF.EZCAP_CLASS_CODE
INNER JOIN AOS_PROVIDER_CONTRACT_REF_TABLE ON
A.[EZCAP.PROV_MASTERS.PROVID] = AOS_PROVIDER_CONTRACT_REF_TABLE.[EZCAP.PROV_MASTERS.PROVID]
LEFT JOIN ProviderContracts_BACKUP
ON ProviderContracts_BACKUP.PRVNO = A.[EZCAP.PROV_MASTERS.PROVID]
Where ProviderContracts_BACKUP.PRVNO IS NULL
AND ProviderContracts_BACKUP.GRPID IS NULL

Lowfatspread

which tables did you add ?

please explain the relation ships between the tables..?

what are you trying to show?

Dishan Fernando

Yes. Post the Table Structure. or any result set .

ASKER CERTIFIED SOLUTION

fromapu

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Hilaire

select * from four tables will return a huge composite row as a concatenation of data in each and every joined table

I'm sure you don't need all this stuff

Adding a 'distinct ' in the select will not change anything, because the values from joined tables will always be different (unless they have duplicate rows)

To decrease the number of rows, you have to define what you need first

Select distinct A.* will return less rows

also I think the end of the query

LEFT JOIN ProviderContracts_BACKUP
ON ProviderContracts_BACKUP.PRVNO = A.[EZCAP.PROV_MASTERS.PROVID]
Where ProviderContracts_BACKUP.PRVNO IS NULL
AND ProviderContracts_BACKUP.GRPID IS NULL

could be replaced by

where not exists (select 'x' from ProviderContracts_BACKUP where ProviderContracts_BACKUP.PRVNO = A.[EZCAP.PROV_MASTERS.PROVID])

HTH

Hilaire