How to Determine Product Visibility in MySQL Based on Manufacturer Settings?

MySQL Select Statement: Determining Product Visibility Using Manufacturer Settings

Your goal is to construct a query that calculates the visibility of products based on rules defined in the manufacturer table. Specifically, you want to determine where a product can be viewed (e.g., Everywhere, Canada only, or USA only).

The initial query attempts to use an IF/ELSEIF statement within a subquery to calculate the visibility. However, this approach is incorrect. Instead, consider using a CASE statement to evaluate the product's status and assign the appropriate visibility level.

The provided CASE statement has a minor issue: it always evaluates the first WHEN condition regardless of the product's status. To fix this, remove the AND condition from each WHEN clause.

The following revised query should provide the desired result:

SELECT
  t2.company_name,
  t2.expose_new,
  t2.expose_used,
  t1.title,
  t1.seller,
  t1.status,
  CASE status
      WHEN 'New' THEN t2.expose_new
      WHEN 'Used' THEN t2.expose_used
      ELSE NULL
  END as 'expose'
FROM
  `products` t1
JOIN manufacturers t2
  ON
    t2.id = t1.seller
WHERE
  t1.seller = 4238

This query evaluates the status of each product, whether 'New' or 'Used', and returns the corresponding visibility level defined in the manufacturer table.

The above is the detailed content of How to Determine Product Visibility in MySQL Based on Manufacturer Settings?. For more information, please follow other related articles on the PHP Chinese website!