Joining 3 tables in SQL Server

Question

I have three tables namely: user, special_order, corp_order.

I want a result where it can display all orders placed by a user in both order tables.

My SQL statement is:

SELECT 
    u.user_id,
    c.user_id,
    s.user_id
FROM
    corp_user u
JOIN 
    special_order s ON s.user_id = u.user_id
JOIN 
    corp_orders c ON c.user_id = u.user_id;

which is returning unnecessary data.

This post will probably help https://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join — jdow, Jan 08 '18 at 01:53
@jdow both the tables. A user have some order placed in `special_orders` and some orders in `corp_orders` — , Jan 08 '18 at 01:58
You probably are better off having 2 separate queries as I presume that there is no relationship between `special` and `corporate` orders — jdow, Jan 08 '18 at 02:02

score 0 · Accepted Answer · answered Jan 08 '18 at 02:20

What you want is all orders, regardless of whether they are special orders or coprporate orders. Decide what columns you want from each corp_user table, and matching columns for orders from each order table and then do two separate queries that are linked by UNION ALL. (You want UNION ALL, not UNION because otherwise if there are exact matches in all fields, the duplicates would be eliminated.

Example for illustration:

SELECT
    u.user_id,
    s.order_id
FROM
    corp_user u 
    INNER JOIN special_order s on u.user_id = s.user_id
UNION ALL
SELECT
    u.user_id,
    c.order_id
FROM
    corp_user u 
    INNER JOIN corp_order c on u.user_id = c.user_id

Note: fields must match exactly - not necessarily in names, but in position. Names from first query will be used.

Joining 3 tables in SQL Server

1 Answers1