Subqueries in the FROM Clause

--bcaec54308ba9e7caa04a133f921
Content-Type: text/plain; charset=ISO-8859-1

Hello All,

Tables:
# relevant fields
invitations: donation_pledge, paid (boolean), currency_id
currencies: code


I am trying to subtract the paid amounts from the amounts pledged using a
subquery in the FROM clause but am having problems and am going blind. Plus,
My SQL is weak at present.

I was hoping a SQL ninja could have a look for me and tell me where I'm
going wrong.

I hope the following formats nicely for you to see what I've done ....

This shows the amounts pledged grouped by (currency) code

SELECT SUM(i.donation_pledge), c.code
FROM invitations i LEFT JOIN currencies c ON i.currency_id = c.id
WHERE i.meeting_id = 934311021
GROUP BY c.code;
+------------------------+------+
| sum(i.donation_pledge) | code |
+------------------------+------+
| 11170 | BRL |
| 2997 | EUR |
+------------------------+------+

This shows the amounts paid grouped by (currency) code

SELECT SUM(i.donation_pledge), c.code
FROM invitations i LEFT JOIN currencies c ON i.currency_id = c.id
WHERE i.meeting_id = 934311021 AND paid = true
GROUP BY c.code;

+------------------------+------+
| sum(i.donation_pledge) | code |
+------------------------+------+
| 70 | BRL |
| 999 | EUR |
+------------------------+------+

And this is supposed to show the amounts outstanding but it has doubled the
values and subtracted 70 from each each value (the BRL currency code amount)

SELECT sum(donation_pledge) - paid_donation_pledge
FROM (SELECT i2.meeting_id, sum(donation_pledge) AS paid_donation_pledge

FROM invitations i2 LEFT JOIN currencies c2 ON
i2.currency_id = c2.id
WHERE i2.meeting_id = 934311021 AND i2.paid = true
GROUP BY c2.code ) AS i2 LEFT JOIN invitations i ON i2.meeting_id
= i.meeting_id
LEFT JOIN currencies c
ON i.currency_id = c.id
GROUP BY c.code;

+---------------------------------------------+
| sum(donation_pledge) - paid_donation_pledge |
+---------------------------------------------+
| 22270 |
| 5924 |
+---------------------------------------------+

Many thanks in advance

-ants

--bcaec54308ba9e7caa04a133f921--

Re: Subqueries in the FROM Clause

Hallo everybody!


Ants Pants wrote:
> Hello All,
>
> Tables:
> # relevant fields
> invitations: donation_pledge, paid (boolean), currency_id
> currencies: code
>
>
> I am trying to subtract the paid amounts from the amounts pledged using a
> subquery in the FROM clause but am having problems and am going blind. Plus,
> My SQL is weak at present.
>
> I was hoping a SQL ninja could have a look for me and tell me where I'm
> going wrong.
>
> I hope the following formats nicely for you to see what I've done ....
>
> This shows the amounts pledged grouped by (currency) code
>
> SELECT SUM(i.donation_pledge), c.code
> FROM invitations i LEFT JOIN currencies c ON i.currency_id = c.id
> WHERE i.meeting_id = 934311021
> GROUP BY c.code;
> +------------------------+------+
> | sum(i.donation_pledge) | code |
> +------------------------+------+
> | 11170 | BRL |
> | 2997 | EUR |
> +------------------------+------+
>
> This shows the amounts paid grouped by (currency) code
>
> SELECT SUM(i.donation_pledge), c.code
> FROM invitations i LEFT JOIN currencies c ON i.currency_id = c.id
> WHERE i.meeting_id = 934311021 AND paid = true
> GROUP BY c.code;
>
> +------------------------+------+
> | sum(i.donation_pledge) | code |
> +------------------------+------+
> | 70 | BRL |
> | 999 | EUR |
> +------------------------+------+
>
> And this is supposed to show the amounts outstanding but it has doubled the
> values and subtracted 70 from each each value (the BRL currency code amount)
>
> SELECT sum(donation_pledge) - paid_donation_pledge
> FROM (SELECT i2.meeting_id, sum(donation_pledge) AS paid_donation_pledge
>
> FROM invitations i2 LEFT JOIN currencies c2 ON
> i2.currency_id = c2.id
> WHERE i2.meeting_id = 934311021 AND i2.paid = true
> GROUP BY c2.code ) AS i2 LEFT JOIN invitations i ON i2.meeting_id
> = i.meeting_id
> LEFT JOIN currencies c
> ON i.currency_id = c.id
> GROUP BY c.code;
>
> +---------------------------------------------+
> | sum(donation_pledge) - paid_donation_pledge |
> +---------------------------------------------+
> | 22270 |
> | 5924 |
> +---------------------------------------------+

AFAICS, you are missing the equality condition on the currency between
the subquery and the other tables. This would explain why the 70 is
subtracted not only from the BRL value but also from the EUR.
Off-hand, I have no explanation for the doubling of the sums, but I have
never used subqueries in the FROM clause.

Others might know more about this, but telling the version you are using
might be helpful for them.


That said, IMO you are doing it much more complicated than necessary:
As your "invitations" table that lists the pledges also has a field
"paid", it seems you could calculate the amounts outstanding in the same
way as those paid, just changing the condition on "paid":

SELECT SUM(i.donation_pledge), c.code
FROM invitations i LEFT JOIN currencies c ON i.currency_id = c.id
WHERE i.meeting_id = 934311021 AND paid != true
GROUP BY c.code;

Of course, details will depend on what you enter in "paid", and you must
take care of NULL values.


HTH,
Joerg

--
Joerg Bruehe, MySQL Build Team, joerg.bruehe@oracle.com
ORACLE Deutschland B.V. & Co. KG, Komturstrasse 18a, D-12099 Berlin
Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven
Amtsgericht Muenchen: HRA 95603

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Subqueries in the FROM Clause

--bcaec543067cb211e804a135a4a2
Content-Type: text/plain; charset=ISO-8859-1

On 18 April 2011 20:19, Joerg Bruehe wrote:

> Hallo everybody!
>
>
> Ants Pants wrote:
> > Hello All,
> >
> > Tables:
> > # relevant fields
> > invitations: donation_pledge, paid (boolean), currency_id
> > currencies: code
> >
> >
> > I am trying to subtract the paid amounts from the amounts pledged using a
> > subquery in the FROM clause but am having problems and am going blind.
> Plus,
> > My SQL is weak at present.
> >
> > I was hoping a SQL ninja could have a look for me and tell me where I'm
> > going wrong.
> >
> > I hope the following formats nicely for you to see what I've done ....
> >
> > This shows the amounts pledged grouped by (currency) code
> >
> > SELECT SUM(i.donation_pledge), c.code
> > FROM invitations i LEFT JOIN currencies c ON i.currency_id = c.id
> > WHERE i.meeting_id = 934311021
> > GROUP BY c.code;
> > +------------------------+------+
> > | sum(i.donation_pledge) | code |
> > +------------------------+------+
> > | 11170 | BRL |
> > | 2997 | EUR |
> > +------------------------+------+
> >
> > This shows the amounts paid grouped by (currency) code
> >
> > SELECT SUM(i.donation_pledge), c.code
> > FROM invitations i LEFT JOIN currencies c ON i.currency_id = c.id
> > WHERE i.meeting_id = 934311021 AND paid = true
> > GROUP BY c.code;
> >
> > +------------------------+------+
> > | sum(i.donation_pledge) | code |
> > +------------------------+------+
> > | 70 | BRL |
> > | 999 | EUR |
> > +------------------------+------+
> >
> > And this is supposed to show the amounts outstanding but it has doubled
> the
> > values and subtracted 70 from each each value (the BRL currency code
> amount)
> >
> > SELECT sum(donation_pledge) - paid_donation_pledge
> > FROM (SELECT i2.meeting_id, sum(donation_pledge) AS
> paid_donation_pledge
> >
> > FROM invitations i2 LEFT JOIN currencies c2 ON
> > i2.currency_id = c2.id
> > WHERE i2.meeting_id = 934311021 AND i2.paid = true
> > GROUP BY c2.code ) AS i2 LEFT JOIN invitations i ON
> i2.meeting_id
> > = i.meeting_id
> > LEFT JOIN currencies
> c
> > ON i.currency_id = c.id
> > GROUP BY c.code;
> >
> > +---------------------------------------------+
> > | sum(donation_pledge) - paid_donation_pledge |
> > +---------------------------------------------+
> > | 22270 |
> > | 5924 |
> > +---------------------------------------------+
>
> AFAICS, you are missing the equality condition on the currency between
> the subquery and the other tables. This would explain why the 70 is
> subtracted not only from the BRL value but also from the EUR.
> Off-hand, I have no explanation for the doubling of the sums, but I have
> never used subqueries in the FROM clause.
>
> Others might know more about this, but telling the version you are using
> might be helpful for them.
>
>
> That said, IMO you are doing it much more complicated than necessary:
> As your "invitations" table that lists the pledges also has a field
> "paid", it seems you could calculate the amounts outstanding in the same
> way as those paid, just changing the condition on "paid":
>
> SELECT SUM(i.donation_pledge), c.code
> FROM invitations i LEFT JOIN currencies c ON i.currency_id = c.id
> WHERE i.meeting_id = 934311021 AND paid != true
> GROUP BY c.code;
>
> Of course, details will depend on what you enter in "paid", and you must
> take care of NULL values.
>
>
> HTH,
> Joerg
>
> --
> Joerg Bruehe, MySQL Build Team, joerg.bruehe@oracle.com
> ORACLE Deutschland B.V. & Co. KG, Komturstrasse 18a, D-12099 Berlin
> Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven
> Amtsgericht Muenchen: HRA 95603
>
Joerg,

How embarrassing!! That's what you get for not taking breaks and having
blinker vision. I'm such a fool!!

Right now, I am the colour of my shirt. A very bright red!!

Have a nice evening

--bcaec543067cb211e804a135a4a2--