CODEWITHSUNDEEP
mysql
pbhle
pbhle

Reputation: 2936

how to select multiple columns within subquery in mysql

I want to define query something like this:

select sum(column) from table1 where id in(select column1, column2 from table2);

How can I do this?

Update::

table1(id | base_p_id | additional_p_id)

     1|    2    | 4

     2|    2    | 3

table2(id | desc | cost)

   2  |   -     |1200

   4  |  -      |400

Now base_p_id & additional_p_id is fk of table2 , and I want to take the sum of the cost

like

select sum(cost) from table2 where id in(select base_p_id ,additional_p_id) from table1 where id=1);

-Thanks.

Upvotes: 0

Views: 192

Answers (2)

Ike Walker
Ike Walker

Reputation: 65537

You can do this with a join. This will also be much faster than using IN with a sub-query:

select sum(table1.column) 
from table1 
inner join table2 on table2.column1 = table1.id or table2.column2 = table1.id

Upvotes: 1

Anthony Raymond
Anthony Raymond

Reputation: 7862

you can use UNION :

SELECT
    sum(column)
FROM
    table1
WHERE
    id IN (SELECT col1 FROM table2 UNION SELECT col2 FROM table 2)

Upvotes: 1

Related Questions