CODEWITHSUNDEEP
sqlsql-servert-sqljoin
gkc
gkc

Reputation: 459

Joining multiple table values into one table in order to see things clearly

I have 4 tables:

Orders:

OrderID Detail  Discount
------------------------
1001    xxx     True
1002    xxx     True
1003    xxx     True
1004    xxx     False
1005    xxx     True
1006    xxx     True

OrderDiscounts:

OrderID DiscountTypeID  DiscountID
----------------------------------
1001     1              8
1002     2              12
1003     1              9
1005     2              13
1006     2              9

Coupons (DiscountTypeID = 1):

CouponID    Title
------------------------
8           CouponTitle8
9           CouponTitle9

Campaigns (DiscountTypeID = 2):

CampaignID  Title
--------------------------
9           CampaignTitle9
12          CampaignTitle12
13          CampaignTitle13

I need a query that will merge all 4 tables into 1 table which will give some result like:

Result:

OrderID Discount    DiscountType    DiscountTitle
-----------------------------------------------------
1001    True        Coupon          CouponTitle8
1002    True        Campaign        CampaignTitle12
1003    True        Coupon          CouponTitle9
1004    False               
1005    True        Campaign        CampaignTitle13
1006    True        Campaign        CampaignTitle9

Note that some Coupon ID may exist as a Campaign ID. Like '9' exists for both as CouponID and CampaignID in this scenario..

In addition to the query required, a proper explanation about how/why using such commands while building the query would be great as I don't just look for an answer but I'd like to handle similar scenarios by myself as well. Thanks!

Upvotes: 1

Views: 108

Answers (2)

www
www

Reputation: 4391

It should look similar to below:

SELECT Orders.OrderID,
CASE when OrderDiscounts.OrderID is NULL THEN False ELSE True end,
CASE when OrderDiscounts.DiscountTypeID  = 1 THEN 'Coupon' 
     when OrderDiscounts.DiscountTypeID  = 2 THEN 'Campaign' 
     else '' end,
CASE WHEN OrderDiscounts.DiscountTypeID  = 1 THEN Coupons.Title 
     WHEN OrderDiscounts.DiscountTypeID  = 2 THEN Campaigns.Title  
     ELSE '' end
FROM Orders
LEFT JOIN OrderDiscounts on Orders.OrderID = OrderDiscounts.OrderID
LEFT JOIN Coupons on OrderDiscounts.DiscountID = Coupons.CouponID
LEFT JOIN Campaigns on OrderDiscounts.DiscountID = Campaigns.CampaignID

Upvotes: 1

brazilianldsjaguar
brazilianldsjaguar

Reputation: 1449

The following code should work - I've done it with a little modification: I've added a 'DiscountType' table.

I understand that maybe all you're looking for is a quick and easy answer, but I think the data schema could be setup better. Having an entire table for each discount type is not scalable nor flexible, and queries like the ones suggested will only get hairier, nastier, and meaner as time goes on. Of course, it all depends on your requirements.

SELECT
  OrderId = o.OrderId,
  Discount = CASE WHEN o.Discount = 1 THEN 'True' ELSE 'False' END,
  DiscountType = COALESCE(dt1.DiscountType, dt2.DiscountType, ''),
  DiscountTitle = COALESCE(coup.Title, camp.Title, '')
FROM
  Orders o
  LEFT JOIN 
    (OrderDiscounts od1       
     JOIN Coupons coup ON coup.CouponID = od1.DiscountID
     LEFT JOIN DiscountType dt1 ON dt1.DiscountTypeId = od1.DiscountTypeId
     ) ON o.OrderId = od1.OrderId
       AND o.Discount = 1
       AND od1.DiscountTypeID = 1
  LEFT JOIN 
    (OrderDiscounts od2 
     JOIN Campaigns camp ON camp.CampaignID = od2.DiscountID
     LEFT JOIN DiscountType dt2 ON dt2.DiscountTypeId = od2.DiscountTypeId
     ) ON 
       od2.OrderID = o.OrderID 
       AND o.Discount = 1
       AND od2.DiscountTypeID = 2

See for yourself here at SqlFiddle.

Upvotes: 2

Related Questions