Select multiple columns from another table depending on value of column

Question

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..

do 2 joins to the address table, one on billing addy 1 on shipping. — Daniel E.
– Daniel E., Commented Dec 6, 2017 at 20:01

Chris Albert · Accepted Answer · 2017-12-06 20:10:20Z

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

Jay · Accepted Answer · 2017-12-06 20:01:46Z

0

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

answered Dec 6, 2017 at 20:01

Jay

1,0396 gold badges23 silver badges43 bronze badges

Comments

Krishnraj Rana · Accepted Answer · 2017-12-06 20:03:49Z

0

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

answered Dec 6, 2017 at 20:03

Krishnraj Rana

6,6583 gold badges33 silver badges37 bronze badges

Collectives™ on Stack Overflow

Select multiple columns from another table depending on value of column

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related