Use the result from a query table into another query

Question

There are 3 different tables and from first table i get the "contact_id" and based on this i would like to do the SUM into the next 2 tables and then MINUS the tables as in the code bellow.
I am trying to use the result "contact_id" from the first query into the following queries

SELECT ( SELECT `id` FROM `civicrm_contact` WHERE `first_name` LIKE 'test2' ) AS contact_id

=============================================================

SELECT ( SELECT SUM(`total_amount`) FROM `civicrm_contribution` WHERE `contact_id`= ) - ( SELECT SUM(`fee_amount`) FROM `civicrm_participant` WHERE `contact_id`= ) As RemainingPoints

d.danailov · Accepted Answer · 2013-09-03 04:30:18Z

You need to use subquery :

SELECT ( SELECT SUM(`total_amount`) FROM `civicrm_contribution` WHERE `contact_id`= (SELECT `id` FROM `civicrm_contact` WHERE `first_name` LIKE 'test2' ) AS contact_id) ) - ( SELECT SUM(`fee_amount`) FROM `civicrm_participant` WHERE `contact_id`= (SELECT `id` FROM `civicrm_contact` WHERE `first_name` LIKE 'test2' ) AS contact_id) ) As RemainingPoints

The query may fail as the inner sub query can return more than one value. Use in instead of =

heretolearn · Accepted Answer · 2013-09-03 04:35:01Z

This should solve your problem :

SELECT ( SELECT SUM(`total_amount`) FROM `civicrm_contribution` WHERE `contact_id` in (SELECT `id` FROM `civicrm_contact` WHERE `first_name` LIKE 'test2' ) AS contact_id) ) - ( SELECT SUM(`fee_amount`) FROM `civicrm_participant` WHERE `contact_id` in (SELECT `id` FROM `civicrm_contact` WHERE `first_name` LIKE 'test2' ) AS contact_id) ) As RemainingPoints

Thanks for your elaborated answer! I finally changed the structure and I used sub select in my query. thanks for the distinct hint which I did not know.

Manigandan Arjunan · Accepted Answer · 2013-09-03 04:37:59Z

You should limit the result of the subquery to 1 otherwise it will result in an error, the best way is to match the name using '=' instead of 'like'

SELECT ( SELECT SUM(`total_amount`) FROM `civicrm_contribution` WHERE `contact_id`= (SELECT `id` FROM `civicrm_contact` WHERE `first_name` LIKE 'test2' limit 1 ) AS contact_id) ) - ( SELECT SUM(`fee_amount`) FROM `civicrm_participant` WHERE `contact_id`= (SELECT `id` FROM `civicrm_contact` WHERE `first_name` LIKE 'test2' limit 1 ) AS contact_id) ) As RemainingPoints

Indra Yadav · Accepted Answer · 2013-09-03 04:38:52Z

0

This could help you

SELECT @s:=1+1 ,@s + 4,@s-1

o/p -@s:=1+1|@s + 4 | @s-1

 2 | 6| 1

answered Sep 3, 2013 at 4:38

Indra Yadav

5965 silver badges22 bronze badges

Collectives™ on Stack Overflow

Use the result from a query table into another query

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Related