sql Two columns of one table references to the same column of the other table

Question

I have two tables table : Product where cost1_id is not null and cost2_id is nullable

   id    | cost1_id | cost2_id
   1     | 1        | 2
   2     | 3        | 

table : Cost

   id    | value    | currency
   1     | 15       | EUR
   2     | 20       | USD
   3     | 100      | TND

I want to have this result without using join (I would rather work with UNION because I have large database)

product_id | cost1_value | cost1_currency | cost2_value | cost2_currency 
     1     | 15          | EUR            |  20         |  USD
     2     | 100         | TND            |             |

Answer 1

One option I can think of to minimise joins is...

SELECT
    product.id    AS product_id,
    MAX(CASE WHEN cost.id = product.cost1_id THEN cost.value    END)   AS cost1_value,
    MAX(CASE WHEN cost.id = product.cost1_id THEN cost.currency END)   AS cost1_currency,
    MAX(CASE WHEN cost.id = product.cost2_id THEN cost.value    END)   AS cost2_value,
    MAX(CASE WHEN cost.id = product.cost2_id THEN cost.currency END)   AS cost2_currency
FROM
    product
LEFT JOIN
    cost
        ON cost.id IN (product.cost1_id, product.cost2_id)
GROUP BY
    product.id

That said, the use of IN() may make that slower than just joining twice...

Ensure you have indexes on your tables. They are the way to optimise joins, not trying to avoid them...

• In this case you probably only need a clustered primary key in the Cost table...

As such, I strongly recommend you at least try the "normal" way of doing this...

SELECT
    product.id    AS product_id,
    c1.value      AS cost1_value,
    c1.currency   AS cost1_currency,
    c2.value      AS cost2_value,
    c2.currency   AS cost2_currency
FROM
    product
LEFT JOIN
    cost    c1
        ON c1.id = product.cost1_id
LEFT JOIN
    cost    c2
        ON c2.id = product.cost2_id

EDIT:

Another esoteric option could be...

SELECT
    product_id,
    MAX(cost1_value)       AS cost1_value,
    MAX(cost1_currency)    AS cost1_currency,
    MAX(cost2_value)       AS cost2_value,
    MAX(cost2_currency)    AS cost2_currency
FROM
(
    SELECT
        product.id                                       AS product_id,
        cost.value                                       AS cost1_value,
        cost.currency                                    AS cost1_currency,
        CASE WHEN 1=0 THEN cost.value    ELSE NULL END   AS cost2_value,
        CASE WHEN 1=0 THEN cost.currency ELSE NULL END   AS cost2_currency
    FROM
        product
    LEFT JOIN
        cost
            ON cost.id = product.cost1_id

    UNION ALL

    SELECT
        product.id                                       AS product_id,
        NULL                                             AS cost1_value,
        NULL                                             AS cost1_currency,
        cost.value                                       AS cost2_value,
        cost.currency                                    AS cost2_currency
    FROM
        product
    INNER JOIN
        cost
            ON cost.id = product.cost2_id
)
    unioned
GROUP BY
    product_id

Then create these indexes...

CREATE INDEX ix_product_cost1 ON product(cost1_id, id);
CREATE INDEX ix_product_cost2 ON product(cost2_id, id);

This might be slightly faster, but at the cost of significantly increased complexity which will become a maintenance headache in the future.

Answer 2

Try this Answer, This answer is for SQL Server. Try the same method in PostgreSQL.

CREATE table #Product(ID INT,Cost1_id INT,Cost2_id INT)
INSERT INTO #Product VALUES(1,1,1)
INSERT INTO #Product VALUES(2,3,0)

CREATE table #Cost(ID INT,Value INT,currency VARCHAR(10))
INSERT INTO #Cost VALUES(1,15,'EUR')
INSERT INTO #Cost VALUES(2,20,'USD')
INSERT INTO #Cost VALUES(3,100,'TND')

SELECT ID product_id,MAX(cost1_value)cost1_value,MAX(cost1_Currency)cost1_Currency
        ,MAX(cost2_value)cost2_value,MAX(cost2_Currency)cost2_Currency
FROM(
    SELECT P.ID,C.Value cost1_value,C.Currency cost1_Currency,0 AS cost2_value,'' AS cost2_Currency
    from #Cost C, #Product P
    WHERE P.Cost1_id=C.Id
    UNION ALL
    SELECT P.ID,0 AS cost2_value,'' AS cost2_Currency,C.Value ,C.Currency 
    from #Cost C, #Product P
    WHERE P.Cost2_id=C.Id
    )D
GROUP BY ID

Hope this helps you.