CODEWITHSUNDEEP
mysql
remus
remus

Reputation: 5

Use the result from a query table into another query

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

Upvotes: 0

Views: 78

Answers (4)

Indra Yadav
Indra Yadav

Reputation: 600

This could help you

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

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

       2  |    6|   1

Upvotes: 0

Manigandan Arjunan
Manigandan Arjunan

Reputation: 2265

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

Upvotes: 0

heretolearn
heretolearn

Reputation: 6555

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

Upvotes: 0

d.danailov
d.danailov

Reputation: 9810

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

Upvotes: 1

Related Questions