Jochem van Dieten <[EMAIL PROTECTED]> wrote on 06/22/2005 07:34:30 AM:
> On 6/22/05, David Kagiri wrote:
> > When i run the queries below they all work just fine
> >
> > SELECT sum(consultation)+ sum(laboratory) FROM nairobi,
> familymembers WHERE familymembers.dependantid = nairobi.memberid and
> familymembers.memberid = "AKI00001"
> >
> > SELECT sum(consultation)+ sum(laboratory) FROM riftvalley,
> familymembers WHERE familymembers.dependantid = riftvalley.memberid
> and familymembers.memberid = "AKI00001"
> >
> > SELECT sum(consultation)+ sum(laboratory) FROM coast,familymembers
> WHERE familymembers.dependantid = coast.memberid and familymembers.
> memberid = "AKI00001"
> >
> > But i would like to run the querys above as one query that will
> return a value that will be equivalent to the sum of the values of
> those queries. i hope its possible. is it? i am using MySQL version
4.1.7
> SELECT sum(consultation)+ sum(laboratory)
> FROM familymembers fm
> INNER JOIN coast ON fm.dependantid = coast.memberid
> INNER JOIN riftvalley ON fm.dependantid = riftvalley.memberid
> INNER JOIN nairobi ON fm.dependantid = nairobi.memberid
> WHERE fm.memberid = "AKI00001"
> Jochem
I hate to be the bearer of bad news but unless there is a very special
arrangement of the data in these tables, that query is doomed to failure.
It will fail because of HOW joined tables are actually joined. I will try
to be brief (you will need to un-wrap some of this to help it make sense)
Here is some sample data and an example why this query probably won't
work:
familymembers (dependantid)
----------------
AKI00001
coast (memberid, consultation, laboratory)
----------------
AKI00001, 4, 4
AKI00001, 4, 15
riftvalley (memberid, consultation, laboratory)
----------------
AKI00001, 2, 6
AKI00001, 2, 9
SELECT *
FROM familymembers fm
INNER JOIN coast c ON fm.dependantid = coast.memberid
INNER JOIN riftvalley r ON fm.dependantid = riftvalley.memberid
WHERE fm.dependantid = 'AKI00001';
--------------------------------------------------------------
(fm.dependantid, c.memberid, c.consultation, c.laboratory, r.memberid,
r.consultation, r.laboratory)
--------------------------------------------------------------
AKI00001,AKI00001, 4, 4, AKI00001, 2, 6
AKI00001,AKI00001, 4, 4, AKI00001, 2, 9
AKI00001,AKI00001, 4, 15, AKI00001, 2, 6
AKI00001,AKI00001, 4, 15, AKI00001, 2, 9
First, the column names in Jigal's query will be ambiguous as both `coast`
and `riftvalley` provide columns called `consultation` and `laboratory` to
the query.
Second, because of the Cartesian product used to compute the JOINed
dataset, you wind up with duplicates of the rows from both `coast` and
`riftvalley`. The final SUM() could be off by some multiple based on the
numbers of rows in each table.
This is a tricky problem to solve because it requires a SUM() of a SUM().
The results of the first SUM() queries, I guess you could refer to them as
subtotals, are computed on a per-table basis (each table representing a
service area) and provide a single row of data as their result (you could
expand the query to use a GROUP BY and process multiple `dependantid`s at
once but that would just muddy this particular example). These subtotal
rows need to be added together to produce a "grand total". The "store the
subtotals in a temporary table" method is the one most compatible with the
most MySQL installations (especially with those that do not have
subqueries and "anonymous views") (I will revert to using the original
queries AND I will rewrite them to NOT use the comma-separated, implicit
INNER JOIN syntax)
The first step is to collect all of your regional subtotals into a single
list. I chose to use a temporary table:
CREATE TEMPORARY TABLE tmpTotals
SELECT nairobi.memberid, sum(consultation)+ sum(laboratory) as subtotal
FROM nairobi
INNER JOIN familymembers
ON familymembers.dependantid = nairobi.memberid
WHERE familymembers.memberid = "AKI00001";
INSERT tmpTotals (memberid, subtotal)
SELECT coast.memberid, sum(consultation)+ sum(laboratory) as subtotal
FROM coast
INNER JOIN familymembers
ON familymembers.dependantid = coast.memberid
WHERE familymembers.memberid = "AKI00001";
INSERT tmpTotals (memberid, subtotal)
SELECT riftvalley.memberid, sum(consultation)+ sum(laboratory) as subtotal
FROM riftvalley
INNER JOIN familymembers
ON familymembers.dependantid = riftvalley.memberid
WHERE familymembers.memberid = "AKI00001";
(NOTE: it would have been possible to use a single UNION query and reduce
this step to a single statement but I don't know if David has UNIONs
available)
Now it's academic to use the data in the temporary table to provide the
grand total
SELECT memberid, sum(subtotal) as grandtotal
FROM tmpTotals
GROUP BY memberid;
Of course, when you are through with it, you should always destroy a
temporary table (don't rely on your OS or MySQL to do it for you, it may
never get around to it and you could run low on resources)
DROP TABLE tmpTotals;
I know David wanted a single-statement method of solving this query (and
one may be available) but I cannot predict what version MySQL he is
running and this technique will find the data he wants and it will be
FAST. For one person we should see response times under a second. For
100000 `memberid`s this may take up to 10 or 20 seconds to compute and for
a few million people, it may take a few minutes (even on modest
equipment).
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
