asked on

SQL syntax producing duplicate results

I have an SQL query that is producing overstated records. I have 2 tables, History_1 and History_2 tables. History 1 has multiple data rows matching Call_ID which is creating overstated results. What I need to happen is only to match against 1 of the mutiple data matching records to produce accurate results. I have attached the SQL Syntax. Thanks.

Use Dialer

Select
Facility_Name As "Facility",
History_1.Client As "Client",
Count(History_1.Client) As "Count"

From
History_1,
History_1,
LU_Clients

Where
History_1.Call_ID = History_2.Call_ID and
History_1.Client = LU_Clients.Facility_ID

Group By
History_1.Client,
Facility_Name

Open in new window

tigin44

try this

Use Dialer

Select
Facility_Name As "Facility",
History_1.Client As "Client",
Count(History_1.Client) As "Count"
From History_1
		INNER JOIN History_1 ON History_1.Call_ID = History_2.Call_ID  
		INNER JOIN LU_Clients ON History_1.Client = LU_Clients.Facility_ID
Group By
History_1.Client,
Facility_Name

Open in new window

ASKER CERTIFIED SOLUTION

tigin44

membership

This solution is only available to members.

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

Start Free Trial

HainKurt

is it a type? you used History_1 twice in from part without an alias

try this, and add aliases to the all columns first:

...
From
      History_1 h1,
      History_1 h2,
      LU_Clients lu
Where
...

OB1Canobie

ASKER

Thanks. This fixed the replication.

HainKurt

tigin44, whats the difference between the one accepted and original query?
they should give the same result!...

tigin44

the user has two tables named History_1 and History_2... I used them...

HainKurt

oooh, same observation as 26149492 ;)