CODEWITHSUNDEEP
mysqlselectjoin
bcsteeve
bcsteeve

Reputation: 1001

Trying to get use results of query in another query... possible?

table orders_total with data like so:

+-----------------+-----------+-----------+---------+-------------+
| orders_total_id | orders_id |   title   |  value  |    class    |
+-----------------+-----------+-----------+---------+-------------+
|               1 |         1 | Sub-total | 34.0000 | ot_subtotal |
|               2 |         1 | Tax       | 2.38    | ot_tax      |
|               3 |         2 | Sub-total | 12.0000 | ot_subtotal |
|               4 |         2 | Tax       | 0.60    | ot_tax      |
+-----------------+-----------+-----------+---------+-------------+

table orders with data like so:

+-----------+----------+
| orders_id | currency |
+-----------+----------+
|         1 | USD      |
|         2 | CAD      |
+-----------+----------+

I want to see:

+-----------+----------+-----------+-------+
| orders_id | currency |  subtotal |  tax  |
+-----------+----------+-----------+-------+
|         1 | USD      | 34.0000   | 2.38  |
|         2 | CAD      | 12.0000   | 0.60  |
+-----------+----------+-----------+-------+

I want to grab the two values (where class="ot_subtotal") and insert them into my select for the orders table. But I can't seem to figure out how to query down one table within another query without mysql choking. I've read plenty of questions/answers about this but they all have some detail that throws me off. I think I need a specific example to move forward.

++EDIT++ So sorry, I over-simplified the question and didn't end up asking the question I meant to. I've adjusted above. the trouble I'm having is getting BOTH classes into one output.

Upvotes: 1

Views: 45

Answers (4)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

If you are only concern about generating the pivot table for ot_subtotal and ot_tax then the following should do the trick

select
o.orders_id,
o.currency,
max(case when ot.class = 'ot_subtotal' then value end) as subtotal,
max(case when ot.class = 'ot_tax' then value end) as tax
from orders o
join orders_total ot on ot.orders_id = o.orders_id
group by o.orders_id

Upvotes: 2

Kiran LM
Kiran LM

Reputation: 1379

try this, 

INSERT INTO `new_table`(`orders_id`, `currency`, `value`) 
SELECT o.orders_id,o.currency,ot.value FROM `orders` o 
left join orders_total ot on o.orders_id = ot.orders_id   WHERE ot.class = 'ot_subtotal'

Upvotes: 0

Mureinik
Mureinik

Reputation: 310993

You can join on the orders_id column:

SELECT ot.orders_id, currency, value
FROM   orders_total ot
JOIN   orders o ON ot.orders_id = o.orders_id
WHERE  class = 'ot_subtotal'

Upvotes: 0

Nikhil Batra
Nikhil Batra

Reputation: 3148

You can use inner join of both the tables as:

Select o1.orders_id, o2.currency, o1.value from 
orders_total o1 inner join orders o2 on o1.orders_id = o2.orders_id 
where o1.class = "ot_subtotal"

Upvotes: 0

Related Questions