complex mysql join

[Derleek]

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.

[fenway]

Sorry, TLDR.  Anything simpler?

[Derleek]

TLDR?

[Derleek]

so... it does work... 

 

I needed to divide the dp.Discount_Percent by 100.... it was throwing the whole query off...

 

Delete?