CODEWITHSUNDEEP
sqlsql-server
user1424532
user1424532

Reputation: 95

Select multiple columns from another table depending on value of column

I'm not even sure this is possible, but I have an order table (online store). Within this table, the shipping and billing address columns are ID's which correspond with the address table.

For E.g.

OrderID     ShippingAddressID   BillingAddressID
201800194   21183               21182

The Address table then lists the address information.

AddressID   Address1            City        RegionCode
21182       123 Somewhere Dr    Hometown1   Florida
21183       456 Elsewhere Rd    Hometown2   Florida

I'd like the resulting listing to show something similar to this:

OrderID     BillingAddress1     BillingCity BillingRegionCode   ShippingAddress1    ShippingCity    ShippingRegionCode
201800194   123 Somewhere Dr    Hometown1   Florida             456 Elsewhere Rd    Hometown2       Florida

Is this even possible?

Thanks..

Upvotes: 0

Views: 1801

Answers (3)

Chris Albert
Chris Albert

Reputation: 2507

Join the address table twice. Example with your data below.

CREATE TABLE #Orders (OrderID int, ShippingAddressId int, BillingAddressId int)
CREATE TABLE #Address (AddressID int, Address1 varchar(100), City varchar(100), RegionCode varchar(100))

INSERT INTO #Orders (OrderID, ShippingAddressId, BillingAddressId) VALUES
(201800194 , 21183, 21182)

INSERT INTO #Address (AddressID, Address1, City, RegionCode) VALUES
(21182, '123 Somewhere Dr', 'Hometown1', 'Florida'),
(21183, '456 Elsewhere Rd', 'Hometown2', 'Florida')

SELECT
    ORD.OrderID,
    BILL.Address1 AS 'BillingAddress1',
    BILL.City AS 'BillingCity',
    BILL.RegionCode AS 'BillingRegionCode',
    SHIP.Address1 AS 'ShippingAddress1',
    SHIP.City AS 'ShippingCity',
    SHIP.RegionCode AS 'ShippingRegionCode'
FROM 
    #Orders AS ORD
    LEFT OUTER JOIN #Address AS SHIP
        ON ORD.ShippingAddressId = SHIP.AddressID
    LEFT OUTER JOIN #Address AS BILL
        ON ORD.BillingAddressId = BILL.AddressID

DROP TABLE #Orders
DROP TABLE #Address

Upvotes: 2

Krishnraj Rana
Krishnraj Rana

Reputation: 6656

Yes you can achieve it using join like this - 

SELECT OrderId, Billing.Address1 As BillingAddress1, Shipping.Address1 AS 
ShippingAddress1, ... other column
FROM dbo.Order
LEFT JOIN dbo.Address AS Billing ON Order.BillingAddressID = Billing.AddressID
LEFT JOIN dbo.Address AS Shipping ON Order.ShippingAddressID = Shipping.AddressID

Upvotes: 0

Jay
Jay

Reputation: 1039

Lets assume your Tables are Order, Address.

Select A.OrderID, B1.Address1 As 'BillingAddress1', B1.City As 'BillingCity', B1.RegionCode As 'BillingRegionCode', B2.Address1 As 'ShippingAddress1', B2.City As 'ShippingCity', B2.RegionCode As 'ShippingRegionCode'
FROM Order AS A
Left Join Address  AS B1 On A.BillingAddressID = B1.AddressID
Left Join Address  As B2 On A.ShippingAddressID = B2.AddressID

Hope this Helps

Upvotes: 0

Related Questions