Search code examples
sqldatabasemariadbinnodb

How to set the value of an attribute according to the values of other attributes in SQL?

I am trying to set the value of an attribute according to other attributes' values.

I have the following SQL statement,

SELECT C.course_name, C.language, C.course_price, C.average_rating, C.category, P.name, P.surname, D.percentage, D.start_date, D.end_date, D.is_allowed
FROM course C LEFT OUTER JOIN discount D ON C.course_id=D.discounted_course_id, course_creator CC, person P
WHERE C.course_creator_id=CC.course_creator_id AND CC.course_creator_id=P.person_id

I have the following result,

enter image description here

Expected Result is the same as above, HOWEVER, the first row's course_price is 500.

I want to set the course_price according to the followings:

If current day is less than end_date and is_allowed = 1, Then I want to apply the discount percentage to the course_price. For instance since course_price of the first tuple is 1000, percentage is 50, (let's say end_date is bigger than today's date), and is_allowed=1. Then the course_price should be equal to 500.

Course Relation:

enter image description here

Course Creator Relation:

enter image description here

Discount Relation:

enter image description here

Person Relation:

enter image description here

I want to get All courses with their information and set the course_price of these courses to the discounted price if the discount relation has the course_id of the course and is_allowed is 1, and the end_date of the discount is bigger than today's date

Solution

  • If current day is less than end_date and is_allowed = 1, Then I want to apply the discount percentage to the course_price.

    You seem to want a CASE expression:

    SELECT C.course_name, C.language, C.course_price, C.average_rating, C.category,
     P.name, P.surname,
     D.percentage, D.start_date, D.end_date, D.is_allowed,
     (CASE WHEN current_date < D.end_date and D.is_allowed
           THEN C.course_price * D.percentage
           ELSE c.course_price
      END) as imputed_price
FROM course C LEFT OUTER JOIN
    discount D 
    ON C.course_id = D.discounted_course_id LEFT JOIN
    course_creator CC
    ON C.course_creator_id = CC.course_creator_id LEFT JOIN
    person P
    ON CC.course_creator_id = P.person_id;

    Note that this also fixes the JOIN syntax. Never use comma in the FROM clause!