Problem with Joins

[stressball]

Hi

I have a query that is selecting records from various tables. My problem is that I am getting too many rows returned. There are 8 rows returned, but they are just double ups

QUERY:
select cl.first_name ||' '|| cl.surname as name, cl.phone_home, cl.phone_other, ah.street_no ||' '|| ah.street ||' '|| ah.suburb ||' '|| ah.city as address, ac.activity_desc, ca.eff_date, aa.alert_desc, abd.action_date
from client cl, address_history ah, client_activities ca, activity ac, alerts_by_date abd, all_alerts aa, clnt_mgrs cm, user_orgs uo, user_details ud
-->client id join to retrieve client address information
where cl.client_ref = ah.client_ref
-->client join to client activities table
and cl.client_ref = ca.client_ref
-->activity code join - to get the description from the lookup table
and ca.activity_code = ac.activity_code
-->client id join for the alert by date table
and cl.client_ref = abd.client_ref
-->alert id join for alert details to alert by date table
and abd.alert_id = aa.alert_id
-->action date null meaning alert still valid
and abd.action_date is null
-->client join to client manager table
and cl.client_ref = cm.client_ref
-->client manager table join to user organisation table
and cm.user_org_id = uo.user_org_id
-->join of user login code between user organisation and user details
and uo.usercode = ud.usercode
-->usercode set to admin for testing purposes
and ud.usercode = 'ADMIN'

The output should be a list of activities due in the future
eg client Greg Blob has 4 activities due
And it the output should contain any alerts, these are overdue activities
eg client Greg Blob has 2 alerts
My output doubles everything. I think that because there are 2 alerts, there become 2 of each activity listed. Can I get just the activities and alerts due, or am I going to need multiple select statements? Your ideas/suggestions will be most appreciated.

Here is the output.

Greg Blob 578 9587 789 4561 52 Blobby Way Newtown Oldtown 3 Month Tamariki Ora Check 7/18/01 3 Year Tamariki Ora Check Due
Greg Blob 578 9587 789 4561 52 Blobby Way Newtown Oldtown 3 Month Tamariki Ora Check 7/18/01 Dental Registration Due
Greg Blob 578 9587 789 4561 52 Blobby Way Newtown Oldtown 3 Year Tamariki Ora Check 7/18/01 3 Year Tamariki Ora Check Due
Greg Blob 578 9587 789 4561 52 Blobby Way Newtown Oldtown 3 Year Tamariki Ora Check 7/18/01 Dental Registration Due
Greg Blob 578 9587 789 4561 52 Blobby Way Newtown Oldtown Registration Service 7/18/98 3 Year Tamariki Ora Check Due
Greg Blob 578 9587 789 4561 52 Blobby Way Newtown Oldtown Registration Service 7/18/98 Dental Registration Due
Greg Blob 578 9587 789 4561 52 Blobby Way Newtown Oldtown Registration Service 11/26/01 3 Year Tamariki Ora Check Due
Greg Blob 578 9587 789 4561 52 Blobby Way Newtown Oldtown Registration Service 11/26/01 Dental Registration Due

8 rows returned

 

[rcurva]

One quick solution but a slow execution is to use DISTINCT. Otherwise, you need to include a subquery or more criteria to limit your data to be more distinct.
Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[sem]

This query returns only rows you're asking about: there are no duplicates and all rows are different. Try to use GROUP BY and not to select the details you do not need. If you have 2 similar activities with different dates you can not select both into 1 row with date details.

 

[stressball]

Thanks for the info, I'm separating this query into 2 queries, one to retrieve the activities due, and one to retrieve the overdue activities, as I cannot get these out together without getting incorrect values.