September 14, 2004 at 6:57 pm
Hi Ed
We need to get to the bottom of why so many records are being returned first, I think. This is almost certainly because of the fact that you have a many-to-many relationship in one of your joins (eg the same serviceid appears multiple times in both Services and Actuals). The best way to track this down (if you don't know off the top of your head which relationship is causing the problems), is to build up the joins one at a time, checking the returned record counts as you go. As your primary table is Services, I would start with that one - I imagine the returned record count should remain static as you add the joins.
Regards
Phil
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 15, 2004 at 10:34 am
Thanks for the reply Phil.
It seems that if I do a left join with any one of the tables, I get back the expected number of rows for that particular join. The problem starts as soon as I add another table to join to. I don't know if I am doing the multiple joins incorrectly. I guess I am wondering if in the same select statement I can do the join between the Services table and each one. The joins are not really dependent on each other as I have had in the past.
i.e. . . . from services s left join actuals a on s.serviceid=a.serviceid left join flash f on f.someid=a.someid . . .
This join is almost as if it is 3 separate joins (statements) that I am trying to do all at once. I am not sure if I am making sense or not.
Thanks, Ed
September 16, 2004 at 2:41 pm
When you have multiple joins, the resulting record count will be the product of all the duplicates in all tables.
For instance if you have a central table with joins to two other tables, and one row in the main table matches 10 rows from one table and 10 rows from the other table, there will be 100 rows in the results with all possible combinations.
September 16, 2004 at 5:43 pm
Is there any way to get around this?
September 16, 2004 at 6:18 pm
Maybe. To get rid of any duplicate records that are being returned by your query, try using
select distinct
instead of just select.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 17, 2004 at 3:04 pm
It's probably safe to assume that all the Amount and Comment columns are distinct, so SELECT DISTINCT would not affect the row count.
The only way to "get around" it is to analyze what you're looking for. If a particular value of ServiceID has 5 records in Actuals, 5 in Flash, and 5 in Forecast, the correct result is 125 rows. If that's not what you're looking for then don't join the tables.
Is there some additional relationship between the tables that will limit the rows returned? You can specify multiple join conditions to eliminate unwanted results:
select ...
from a
join b on b.id = a.id
join c on c.id = a.id and c.id2 = b.id2
Do you just want summary amounts from your other tables? Use subqueries to group the data from the subordinate tables. This query will only return one row per serviceid:
select a.serviceid, ActualTotal, FlashTotal, ForecastTotal
from services a
left join (
select serviceid, sum(amount) as ActualTotal
from actuals where <date criteria>
group by serviceid) b on a.serviceid = b.serviceid
left join (
select serviceid, sum(amount) as FlashTotal
from flash where <date criteria>
group by serviceid) c on a.serviceid = c.serviceid
left join (
select serviceid, sum(amount) as ForecastTotal
from forecast where <date criteria>
group by serviceid) d on a.serviceid = d.serviceid
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply