Get id value from same table in query

I have this table setup:

orders:
 id, order_nr

orders_cart:
id, orders_id,ref_backorder

Some data:

Orders:
id order_nr
1  012345
2  0123456

Orders_cart
id, orders_id, ref_backorder
1   2          1
1   1           

the ref_backorder is a reference to another row in the same table.

Expected output:
    id, ref_backorder
    1    012345
    2    null

My query:

SELECT o.id, o.ref_backorder`
FROM orders_cart o 
??
LEFT JOIN orders ON o.ref_backorder` = orders.id 
??

How can i get the ref_backorder as order_nr? ref_backorder is an id from a row. In this case row 1, in row 1 we have an orders_id 2. The value of orders_id 2 (0123456) is the value what I want to show in the query.

Can you join the table to itself?

Answers


Seems the query could be this

select o.order_nr
from order 
inner join orders_cart as c on c.ref_backorder = o.id
where c.orders_id = 2

SELECT A.id, A.orders_id, A.ref_backorder, B.order_nr FROM orders_cart AS A
JOIN orders AS B ON
    A.orders_id = B.id

Need Your Help

What's a better way to write this in RSpec?

ruby-on-rails rspec devise tdd capybara

I wrote a feature spec and that tests when a user signs up. I've got it working, but I feel like the expect block is quite weird.

SOAP and Remote Services with Open Source platforms, Recommendations

web-services open-source soap soa recommendation-engine

I'm a .Net developer by trade, but have done open source development in the past for fun. I need recommendations on which open source languages work particularly better (or even better yet, stable)...