CODEWITHSUNDEEP
mysql
Saswat
Saswat

Reputation: 12836

Retrieving data from 4 tables with if else condition

I have 4 tables.

td_product

|---------------------------------------------------------------------------------|
|  product_id |  product_title  |  collection_id  |  has_item   |  product_price  |
|---------------------------------------------------------------------------------|
|     1       |    Denim Pant   |                 |     1       |  400            |
|---------------------------------------------------------------------------------|
|     2       |    Cool Shirt   |                 |     0       |  500            |
|---------------------------------------------------------------------------------|

td_item

|---------------------------------------------------|
|   item_id  |   product_id  |   color  |   price   |
|---------------------------------------------------|
|     1      |       1       |    RED   |    450    |
|---------------------------------------------------|
|     2      |       1       |    GREEN |    500    |
|---------------------------------------------------|

td_collection

|------------------------------------------------------------|-----------|
|  collection_id  |   collection_type  |   collection_title  |           |
|------------------------------------------------------------------------|
|       1         |       1            |    Summer           |           |
|------------------------------------------------------------------------|
|       2         |       2            |    DENIM            |           |
|------------------------------------------------------------------------|

td_collectioncondition

|---------------------------------------------------------------------------------------------|
|   condition_id  |   collection_id  |   key             |   condition     |    constraint    |
|--------------------------------------------------------------------------|------------------|
|       1         |          2       |   product_title   |   contains      |     Denim        |
|--------------------------------------------------------------------------|------------------|
|       2         |          2       |   product_price   |  is_less_than   |      500         |
|---------------------------------------------------------------------------------------------|

Now let me explain:

Now if condition is that price should be less than 500, then products (whose has_item = 0) will be listed with price less than 0.

But if has_item = 1, then products whose any of the item has pricing less than 500, then that product will be listed.

So I have two queries

First one;

for product with has_item = 0

SELECT * FROM td_product WHERE
            ( ( product_title LIKE '%Denim%' ) 
              AND (product_price < '2000' ) ) 
           AND product_id > 0 AND active = '1'

for product with has_item = 1

SELECT * FROM td_product WHERE
        ( ( product_title LIKE '%Denim%' ) 
          AND ((SELECT max(item_price) FROM td_item WHERE td_item.product_id = td_product.product_id) < '2000' ) ) 
       AND product_id > 0 AND active = '1'

Now I want to make a query which will check both the conditions

I mean if has_item = 0, then 

product_price < 200

If has_item = 1 

(SELECT max(item_price) FROM td_item WHERE td_item.product_id = td_product.product_id) < '2000' )

Say something like using some sort of if else inside the query.

Upvotes: 1

Views: 39

Answers (2)

avisheks
avisheks

Reputation: 1180

I hope this help:

SELECT 
* 
FROM td_product 
WHERE
     product_title LIKE '%Denim%' ) 
     AND (IF(has_item=0,product_price,(SELECT max(item_price) FROM td_item WHERE td_item.product_id = td_product.product_id)) < 2000)
     AND product_id > 0 
     AND active = 1

Upvotes: 1

Lorenz Meyer
Lorenz Meyer

Reputation: 19905

Use a left join : 

SELECT p.* FROM td_product p
LEFT JOIN td_item i ON i.product_id = p.product_id
WHERE
    p.product_title LIKE '%Denim%' ) 

   AND p.product_id > 0 AND p.active = '1' 
GROUP BY p.product_id
HAVING (p.has_item = 0 and p.product_price < 2000) or  (p.has_item = 1 and max(i.item_price) < 2000)

This allows you to test both conditions without if else in the query.

Upvotes: 0

Related Questions