CODEWITHSUNDEEP
sqljoin
user2685157
user2685157

Reputation: 1

SQL help in joining tables

I am stuck at a case which should to be very simple but I cannot work it out with joins :( Let's say I have 3 tables with orders, order states and state names, which are linked by order id and state id.

Table 1 with orders: id_order, number, customer_name, fk_state

Table 2 with order history: fk_order, fk_old_order_state, fk_new_order_state, new_state_date

Table 3 with state names: id_state, state_name

id_state is just a unique number for each state from 1 to 20 and are meaningless without a state name. I want to get a table where I can see old and new state names (not ids!) for all orders in state 'canceled', so to see at which stage the orders were canceled. So i need a table with the follwoing data: order.number, name of the old state, name of the new state, date of the new state.

It's easy to get ids of old and new states and even the name of the first state in the column, but I don't know how to extract in 1 table state names for both new and old states.

I have more similar tasks like this so it would be great if yous helped me out with this one and I could understand the idea. Thanks

Upvotes: 0

Views: 45

Answers (2)

GarethD
GarethD

Reputation: 69819

You just need to join to Table 3 (with the state names) twice. Once for the old state, and once for the new. As such it is important to use table aliases to ensure there is no conflict (below I have used os for Old State, and ns for New State)

SELECT  o.number,
        os.state_name AS old_state_name,
        ns.state_name AS new_state_name,
        oh.new_state_date
FROM    orders o
        INNER JOIN order_History oh
            ON o.id_order = oh.fk_order
        INNER JOIN state_names os
            ON os.id_state = oh.fk_old_order_state
        INNER JOIN state_names ns
            ON ns.id_state = oh.fk_new_order_state;

Upvotes: 2

roman
roman

Reputation: 117606

select
    o.id_order,
    o.number,
    o.customer_name,
    oh.new_state_date,
    sno.state_name as old_state_name
from order_history as oh
    inner join orders as o on o.id_order = oh.fk_order
    inner join state_names as snn on snn.id_state = oh.fk_new_order_state
    left outer join state_names as sno on sno.id_state = oh.fk_old_order_state
where snn.state_name = 'canceled'

Upvotes: 2

Related Questions