MySQL subquery issue

Question

The result i want,'

price1   |  price2 
1. 10000 |  2. -100 
3. 20000 |  4. -200

the queries that will be selected,

select total_price from cloud_payment where user_id='snoopy99' and demandnum= '201307-charge0000040C'
select unpaid_price from gcloud.cloud_service_basic where user_id='snoopy99' and month=7
select total_price from gcloud.cloud_payment where user_id='snoopy99' and demandnum= '201308-charge0000040C'
select unpaid_price from gcloud.cloud_service_basic where user_id='snoopy99' and month=8

I want 1,3 to be in the first column 2,4 to be in the second column.

Any good idea?

Dipesh Parmar · Accepted Answer · 2013-08-21 05:47:53Z

3

Try.

select total_price as price from cloud_payment where user_id='snoopy99' and (demandnum= '201307-charge0000040C' OR demandnum= '201308-charge0000040C')
UNION ALL
select unpaid_price as price from gcloud.cloud_service_basic where user_id='snoopy99' and ( month=7 OR month=8)

answered Aug 21, 2013 at 5:47

Dipesh Parmar

27.3k8 gold badges65 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

rutter · Accepted Answer · 2013-08-21 06:05:13Z

0

You can use JOIN to select a merged record from your service and payment tables, then UNION to select two such records. Something like this:

SELECT total_price, unpaid_price
    FROM  cloud_payment cp
    JOIN  gcloud.cloud_service_basic cs ON cp.user_id = cs.user_id
    WHERE cp.user_id = 'snoopy99'
    AND   cp.demandnum = '201307-charge0000040C'
    AND   cs.month = 7
UNION
SELECT total_price, unpaid_price
    FROM  cloud_payment cp2
    JOIN  gcloud.cloud_service_basic cs2 ON cp2.user_id = cs2.user_id
    WHERE cp2.user_id = 'snoopy99'
    AND   cp2.demandnum = '201308-charge0000040C'
    AND   cs2.month = 8
;

Example SQLFiddle.

answered Aug 21, 2013 at 6:05

rutter

11.5k1 gold badge32 silver badges47 bronze badges

Collectives™ on Stack Overflow

MySQL subquery issue

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related