Derleek Posted January 20, 2010 Share Posted January 20, 2010 I am attempting to join several tables and I'm unclear on what the correct syntax would be. I think I am just not connecting the tables correctly... Tables: Transaction: Transaction_id item_id 1 1 2 2 3 1 4 3 Transaction_Discount: Transaction_id code 1 123 2 222 //type - 0=DiscountPercent | 1 = DiscountValue Discount: code type 123 0 222 1 DiscountPercent: code percent 123 50 DiscountValue: code value 222 2.00 Item: item_id 1 2 3 Item_Discount: Item_ID code 1 123 2 222 Item_Transaction: Transaction_ID Item_ID 1 1 2 2 3 1 4 3 Schedule: Item_ID Price 1 10.00 2 15.00 3 5.00 I would like to calculate the adjusted price when a discount is applied. Here is my query so far... SELECT *, CASE WHEN td.Transaction_ID IS NULL THEN sch.Price WHEN td.Transaction_ID IS NOT NULL AND d.Discount_Type = '0' THEN sched.Price - (sched.Price * dp.Discount_Percent) ELSE sched.Price - dv.Discount_Value END PriceDiscounted FROM ItemTracker_dbo.Transaction t LEFT JOIN ItemTracker_dbo.Purchase p ON t.Transaction_ID = p.Transaction_ID LEFT JOIN ItemTracker_dbo.Item it ON p.Item_ID = it.Item_ID LEFT JOIN ItemTracker_dbo.Schedule sch ON sch.Item_ID = it.Item_ID LEFT JOIN ItemTracker_dbo.Transaction_Discount td ON t.Transaction_ID = td.Transaction_ID LEFT JOIN ItemTracker_dbo.Discount d ON td.Discount_Code = d.Discount_Code LEFT JOIN ItemTracker_dbo.DiscountPercent dp ON dp.Discount_Code = d.Discount_Code LEFT JOIN ItemTracker_dbo.DiscountValue dv ON dv.Discount_Code = d.Discount_Code LEFT JOIN ItemTracker_dbo.Item_Discount id ON id.Discount_Code = d.Discount_Code LEFT JOIN ItemTracker_dbo.Item iq ON id.Item_ID = iq.Item_ID LEFT JOIN ItemTracker_dbo.Schedule sched ON sched.Item_ID = iq.Item_ID The database relations go as follows (at least I think): Transaction -> Transaction_Discount -> Discount -> discountPercent & DiscountValue Transaction -> Transaction_Discount -> Discount -> Item_Discount -> Item -> Schedule Transaction -> Purchase -> Item -> Schedule Do I need to add some conditional logic for the two different discount_types? I'm not sure If I am selecting from the tables in the right order or not... I really am in a bit over my head here, If you look at the logic in the query it should be easy to see what I'm getting at. The query executes and pulls the first condition perfectectly (if no discount was applied). Although I am getting NULL for entries that do have a discount applied. kudos to anyone who stuck with this long post, it's much appreciated. Link to comment https://forums.phpfreaks.com/topic/189211-complex-mysql-join/ Share on other sites More sharing options...
fenway Posted January 20, 2010 Share Posted January 20, 2010 Sorry, TLDR. Anything simpler? Link to comment https://forums.phpfreaks.com/topic/189211-complex-mysql-join/#findComment-998998 Share on other sites More sharing options...
Derleek Posted January 20, 2010 Author Share Posted January 20, 2010 TLDR? Link to comment https://forums.phpfreaks.com/topic/189211-complex-mysql-join/#findComment-999004 Share on other sites More sharing options...
Derleek Posted January 20, 2010 Author Share Posted January 20, 2010 so... it does work... I needed to divide the dp.Discount_Percent by 100.... it was throwing the whole query off... Delete? Link to comment https://forums.phpfreaks.com/topic/189211-complex-mysql-join/#findComment-999014 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.