Getting a value from multiple tables on SQL

Question

I have a table, e.g. ORDERS, each order has a receipt, but the receipts are separated in 3 tables. RECEIPT1, RECEIPT2 and RECEIPT3.

A column on table ORDERS called ORDER_TYPE will define in which of the RECEIPT tables the receipt is.

On my query I need a logic that does something like this: If ORDER_TYPE = 'C' my query gets a value from RECEIPT1, if it's equals 'X' from RECEIPT2 and etc.

I've already tried to use a left join on the 3 tables, but it returns triplicated rows and even if I group all of them there's still a chance of error.

Can anyone help me? Maybe cursors (which I dont know very much how to use)?

[RECEIPTn] should probably be [RECEIPT] with an n column — Alex K.
– Alex K., Commented Jun 17, 2015 at 15:49
It indeed should. The guy who structured the DB made some very bad decisions. I mean, the software is written in Delphi. If it was up to me I would throw everything away and use C#. — N. Reek.
– N. Reek., Commented Jun 17, 2015 at 17:53

Dan · Accepted Answer · 2015-06-17 16:25:40Z

1

Could you post the query you're attempting to use?

It sounds like adding to the ON statement should help. I'm guessing on the o.receipt = r1.receipt part. By adding the "o.order_type = 'c'" to the on statement, it will only join the records from that table when the order_type is what you need it to be.

SELECT *
FROM   ORDERS as o
   LEFT JOIN RECEIPT1 as R1 ON o.receipt = r1.receipt and o.order_type = 'c'
   LEFT JOIN RECEIPT2 as R2 ON o.receipt = r2.receipt and o.order_type = 'x'
   LEFT JOIN RECEIPT3 as R3 ON o.receipt = r3.receipt and o.order_type = '??'

The other answers are using unions, but correctly structured joins will be more efficient and easier to maintain.

This SELECT would return a query similar to the unions, assuming the column "amount" is what you want:

SELECT o.receipt, COALESCE(r1.amount, r2.amount, r3.amount) as amount
FROM   ORDERS as o
   LEFT JOIN RECEIPT1 as R1 ON o.receipt = r1.receipt and o.order_type = 'c'
   LEFT JOIN RECEIPT2 as R2 ON o.receipt = r2.receipt and o.order_type = 'x'
   LEFT JOIN RECEIPT3 as R3 ON o.receipt = r3.receipt and o.order_type = '??'

edited Jun 17, 2015 at 16:25

answered Jun 17, 2015 at 15:50

Dan

5473 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

N. Reek. Over a year ago

I tried this, doesnt work because it returns triplicated rows. But some people here reminded about UNION and now is working fine.

Dan Over a year ago

If you're looking for all of the values in a single column, I've updated my answer. If you're looking to remove multiple rows, you'll need a group by and an aggregate on the columns. If you were getting multiple rows but are not when you sue a union, be aware that unions are distinct, so you could be losing data you don't mean to (blog.sqlauthority.com/2009/03/11/…).

N. Reek. Over a year ago

I used the UNION and it worked fine, because the tables have similar structure. I could use group by, but the way the tables were made i could have some issues. I mean, your answer is fine, the problem is the way the tables were made. It's a very badly designed software.

TheMadDBA · Accepted Answer · 2015-06-17 15:49:39Z

1

Sounds like a bad design just asking for trouble :)

Use three different queries with a union. Not real queries below but you should get the basic idea.

select columns from orders,receipt1 where <joins and filters> and order_type = 'C'
union
select columns from orders,receipt2 where <joins and filters> and order_type = 'X'
union
etc etc

answered Jun 17, 2015 at 15:49

TheMadDBA

4363 silver badges4 bronze badges

4 Comments

N. Reek. Over a year ago

It is a bad design indeed. Actualy, I just now working on this project because the former developer left the country.

FutbolFan Over a year ago

Why not use a case statement instead? Case Statement could be more efficient than union in this scenario. Also, UNION ALL has less overhead than UNION.

N. Reek. Over a year ago

UNION ALL would triplicate the rows, because of that cartesian thing it does, and CASE is used inside the select right? I think it wouldnt fit because my problem is related to accessing different tables depending on the value of the column.

TheMadDBA Over a year ago

UNION ALL "might" duplicate the rows if you have orders that link to multiple receipt tables (regardless of the order_type). If you don't then it should work fine. How much faster it is really depends on how much data is returned by each select. If you have a huge dataset then UNION ALL will skip a sorting/unique step. If the dataset is small then you might save a few ms.

Simone · Accepted Answer · 2015-06-17 15:50:40Z

1

We have to adapt it a little on your needings, but the base could be this

SELECT ...
FROM Orders o INNER JOIN Receipt1 R1 ON o.XXX = R1.XXX
WHERE o.ORDER_TYPE = 'C'
UNION
SELECT ...
FROM Orders o INNER JOIN Receipt2 R2 ON o.XXX = R2.XXX
WHERE o.ORDER_TYPE = 'X'
UNION
SELECT ...
FROM Orders o INNER JOIN Receipt3 R3 ON o.XXX = R3.XXX
WHERE o.ORDER_TYPE = 'Z'

answered Jun 17, 2015 at 15:50

Simone

1,9441 gold badge14 silver badges22 bronze badges

Comments

birdypme · Accepted Answer · 2015-06-17 15:52:05Z

1

I assume the columns are the same in RECEIPT1, RECEIPT2 and RECEIPT3. (otherwise, change the R.* to the explicit columns that are common to the 3 RECEIPT tables)

(SELECT ORDERS.*, R.* FROM ORDERS
 INNER JOIN RECEIPT1 R ON ORDERS.receipt_id=R.id
 WHERE ORDERS.ORDER_TYPE='C')
UNION
(SELECT ORDERS.*, R.* FROM ORDERS
 INNER JOIN RECEIPT2 R ON ORDERS.receipt_id=R.id
 WHERE ORDERS.ORDER_TYPE='X')
UNION
(SELECT ORDERS.*, R.* FROM ORDERS
 INNER JOIN RECEIPT3 R ON ORDERS.receipt_id=R.id
 WHERE ORDERS.ORDER_TYPE='Z')

answered Jun 17, 2015 at 15:52

birdypme

1941 silver badge12 bronze badges

1 Comment

N. Reek. Over a year ago

I totaly forgot about union! That's exactly what I needed. Thank you :)

Jason W · Accepted Answer · 2015-06-17 15:49:01Z

0

You can use a CTE to merge the receipts by order type, then join against the order type you associate to each receipt table. This keeps a simple join against the ORDERS table and let's you pick any of the fields from the matching RECEIPTn table.

;WITH CTE AS (
    SELECT 
        'C' AS ORDER_TYPE,
        *
    FROM RECEIPT1
    UNION ALL
    SELECT 
        'X' AS ORDER_TYPE,
        *
    FROM RECEIPT2
    -- And so on
)
SELECT *
FROM ORDERS O
    INNER JOIN CTE R
        ON R.ORDER_ID = O.ORDER_ID -- Whatever FK is
            AND R.ORDER_TYPE = O.ORDER_TYPE

answered Jun 17, 2015 at 15:49

Jason W

13.2k3 gold badges35 silver badges62 bronze badges

Comments

FutbolFan · Accepted Answer · 2015-06-17 17:57:30Z

0

Since you didn't provide any table structure, and if I understand your issue correctly, you could use CASE statement to get your values based on order_type like this:

SELECT o.orderid
    ,CASE 
        WHEN o.order_type = 'C'
            THEN r1.receipt_value
        WHEN o.order_type = 'X'
            THEN r2.receipt_value
        ELSE r3.receipt_value
        END AS receipt_value
FROM orders o
LEFT JOIN receipt1 r1 ON o.receiptid = r1.id
LEFT JOIN receipt2 r2 ON o.receiptid = r2.id
LEFT JOIN receipt3 r3 ON o.receiptid = r3.id

edited Jun 17, 2015 at 17:57

answered Jun 17, 2015 at 15:49

FutbolFan

13.9k3 gold badges27 silver badges35 bronze badges

2 Comments

TheMadDBA Over a year ago

This should work if you change the INNER to OUTER joins. If there aren't entries in the various receipt tables for all of the orders (doesn't seem like it) then you will not get the proper rows (if any). The other downside for using case is when you want to get 20 columns back instead of just 1. It is just a lot more code in the select compared to a simple union

FutbolFan Over a year ago

@TheMadDBA You are absolutely right about the join. Thanks!

Collectives™ on Stack Overflow

Getting a value from multiple tables on SQL

6 Answers 6

3 Comments

4 Comments

Comments

1 Comment

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

3 Comments

4 Comments

Comments

1 Comment

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related