Joining three tables: Problem with too many records returned

[eddie798]

I require some help with an issue around a query I would like to create. I am working with Sybase Interactive SQL version 7.02. I am retrieving more records than required and my sum is not returning the correct values.

I would like to make the following 2 queries into on:
Query 1:
select c.clientno, p.anncore
from
DBA.contact c LEFT OUTER JOIN DBA.policy p on (c.clientno = p.clientno)
where c.clientno = '1' and
p.anncore > 0

OUTPUT is:
clientno anncore
1 18.48
1 38.88
1 63.24

Query 2)
select c.clientno, i.anncore
from
DBA.contact c LEFT OUTER JOIN DBA.invest i on (c.clientno = i.clientno)
where c.clientno = '1' and
i.anncore > 0
OUTPUT is:
clientno anncore
1 39.12
1 8.28
1 9.6
1 73.2


The combination of the two queries must work with three tables; contact, policy and investment. A client has a clientno on the contact table and the clientno is the primary key. Tables policy and investment contain the clientno as a foreign key. I would like to retrieve the sum of the annual core (anncore) which is a value associated with records on the policy and investment record. (Note: I included clientno =1 for testing purposes but the query would run against all clients in the contact table). To summarize I want to sum the anncomm for each client based on the anncomm in the policy and investment tables associated with the clients.

The combined query is as follows:

select c.clientno, sum(p.anncore), sum(i.anncore)
from
DBA.contact c LEFT OUTER JOIN DBA.policy p on (c.clientno = p.clientno),
DBA.contact c LEFT OUTER JOIN DBA.invest i on (c.clientno = i.clientno)
where c.clientno = '1' and
p.anncore > 0 and
i.anncore > 0
group by c.clientno
having sum(p.anncore)+sum(i.anncore) between '1' AND '10000'

Output:
clientno sum(p.anncore) sum(i.anncore)
1 482.4 390.59

The output is incorrect. Is there something wrong with the syntax. Sum(p.anncore) is three times what it should be (see query 1) and the sum(i.anncore) is three times what it should be (see query 2).

Any help with the syntax is appreciated.

 

[JeanNiBee]

Hi

Try grouping your conditions with braces... I.E.

SELECT c.clientno, sum(p.anncore), sum(i.anncore) 
FROM DBA.contact c 
LEFT OUTER JOIN DBA.policy p 
    ON (c.clientno = p.clientno), DBA.contact c 
LEFT OUTER JOIN DBA.invest i 
    ON (c.clientno = i.clientno)
WHERE c.clientno = '1' 
AND ( 
 p.anncore > 0 
 OR
 i.anncore > 0 
}
GROUP BY c.clientno

Also you may want to check out the Sybase Adaptive (SQL) Server forum on Tek Tips for questions like this. It's much more technical. This forum (Sybase Solutions) is more of a 'how do I use Sybase products for a problem'.

Hope this helps.


-=-=-=-=-=-=-=-=-
For ease of reading, any posted CODE should be wrapped by [ignore][COLOR] and

[/ignore] tags.

Ex:

SELECT 1 from sysobjects