Mysql two tables using 'in'

Hi,
I am having trouble visualizing this query:

I have a table that stores order details:
order_id          code                  value
149396          total                  18.6840
149396          sub_total          10.6200

I pull the data out for reporting purposes like:

select code,value from order_total where order_total.order_id = '149396' and code in ('sub_total','total')

This shows returns

code                 value
sub_total         10.6200
total                 18.6840

All good, but a want to add a second table on the order_id that contains a payment method 'payment_code' and display the result like:

code                     value
sub_total             10.6200
total                     18.6840
payment_code    worldpay

This is my current query but result shows in new column, I can't see how to get the second table as an 'in'

SELECT
order_total.`code`,
order_total.`value`,
order1.payment_code
FROM
order_total
INNER JOIN `order` as order1 ON order1.order_id = order_total.order_id
where order_total.order_id = '153981' and code in ('sub_total','total')

help!!!!
Tony PearceAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
you want to join on that column also, I presume ...
SELECT
ot.`code`,
ot.`value`,
o.payment_code
FROM order_total ot
INNER JOIN `order` as o 
  ON o.order_id = o.order_id
 and ot.code  = o.code
where ot.order_id = '153981' 

Open in new window

0
Tony PearceAuthor Commented:
Hi,

the only join possible is order_id, perhaps a default value set for the 'payment_code' under the 'code' column.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I think then you want to UNION the records, not "JOIN"
SELECT
ot.`code`,
ot.`value`
FROM order_total ot
where ot.order_id = 153981
  and ot.code in ('sub_total','total')
UNION ALL
select 'payment_code',  o.payment_code
 FROM `order` o
where p.order_id = 153981 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Tony PearceAuthor Commented:
Perfect solution, thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.