CODEWITHSUNDEEP
sql-server
Victor
Victor

Reputation: 192

how to select same column with different condition?

I am trying to select data from two tables.
-warehouse has two columns: warehouseId, warehouseName
-transportation has three columns: transporter, warehouseId1, warehouseId2
i want to select from two tables and get warehouseName from table A for warehouseId1 and warehouseId2 here is my code but it does not work.

select a.transporter, b.warehouseName as warehouse1, b.warehouseName as warehouse2
from transportation a, warehouse b
where a.warehouseId1 = b.warehouseId and a.warehouseId2 = b.warehouseId

Upvotes: 3

Views: 14611

Answers (2)

Ross Presser
Ross Presser

Reputation: 6255

It may be clearer to use subselect:

SELECT 
    a.transporter,
    (SELECT warehouseName FROM warehouse WHERE warehouseId=a.warehouseId1) AS warehouse1,
    (SELECT warehouseName FROM warehouse WHERE warehouseId=a.warehouseId2) AS warehouse2
FROM
    transportation a

This will generally use exactly the same query plan as MarcinJuraszek's solutions, but may be a little clearer as to what's going on.

Upvotes: 2

MarcinJuraszek
MarcinJuraszek

Reputation: 125610

You have to add warehouse twice into FROM (just remember to use two different aliases for them):

SELECT
    a.transporter,
    b1.warehouseName as warehouse1,
    b2.warehouseName as warehouse2
FROM
    transportation a,
    warehouse b1,
    warehouse b2
WHERE
        a.warehouseId1 = b1.warehouseId
    AND
        a.warehouseId2 = b2.warehouseId

or use JOIN syntax:

SELECT
    a.transporter,
    b1.warehouseName AS warehouse1,
    b2.warehouseName AS warehouse2
FROM
    transportation a
JOIN
    warehouse b1 ON a.warehouseId1 = b1.warehouseId
JOIN
    warehouse b2 ON a.warehouseId2 = b2.warehouseId

Upvotes: 7

Related Questions