Syntax for MySQL SELECT statement with a subquery within the IF statement

Question

I'm having trouble getting the syntax right for a MySQL SELECT statement with a subquery within the IF statement.

EDIT

I need to include subqueries in the db query if particular values have been specified. In this particular example, if they've specified the Item then only fetch rows where Item and Price also match specific values.

SELECT * FROM Products WHERE Products.Color = :Colors_like
AND IF(Item = IS NOT NULL OR Item != '' 
(Item = :Item AND Products.Price <= (SELECT $MaxPrice_Item FROM Lifestyle WHERE User_id = $User_id) ) 
)

I've included a simplified snippet below with hardcoded values

This works correctly:

SELECT * FROM Products WHERE Color = 'Black'
AND IF (Item = 'Dresses', 1, 0) = 1

This doesn't work (If Item = Dresses, it should check Price)

SELECT * FROM Products WHERE Color = 'Black'
AND IF (Item = 'Dresses' (SELECT * FROM Products WHERE Price < '$300'), 1, 0) = 1

I've tried every format I could think of, but I can't seem to get the statement to work.

"This doesn't work" --- what should it do actually? The query is completely broken and it's hard to guess what you wanted to express with it. PS: WHERE Price < '$300' do you really store prices as a $ char prefixed strings? — zerkms
– zerkms, Commented Nov 23, 2014 at 23:55
@zerkms - I just clarified what it should do :-) (Apologies if I wasn't clear) — Chaya Cooper
– Chaya Cooper, Commented Nov 23, 2014 at 23:58
It still is not clear. Try to explain with domain language. Like: I need to select products that are black and ... and ... PS: you still didn't answer about prices PPS: IF (Item = 'Dresses', 1, 0) = 1 - this makes no sense. So trying to solve it "in the similar way" is wrong by design. — zerkms
– zerkms, Commented Nov 23, 2014 at 23:59
@zerkms - I'm only using the syntax of "...Item = 'Dresses', 1, 0) = 1" because I understood that's how to include an IF statement within a Select statement ;-) What I need to do is a Select statement WHERE ... (i.e. Color) AND IF ... (i.e. Item) THEN ... (i.e. Price). Does that help clarify it? — Chaya Cooper
– Chaya Cooper, Commented Nov 24, 2014 at 0:06
"because I understood that's how to include an IF " --- that's my exact point. You don't need IF and you never needed it. Anyway, nothing to discuss here anymore as Gordon has provided a good answer. (which still implies you need to clarify about how you store prices) — zerkms
– zerkms, Commented Nov 24, 2014 at 0:07

Gordon Linoff · Accepted Answer · 2014-11-24 00:02:50Z

1

You need a something before the subselect:

SELECT *
FROM Products
WHERE Color = 'Black' AND
     IF (Item = 'Dresses' ?? (SELECT * FROM Products WHERE Price < '$300'), 1, 0) = 1
--------------------------^

I am not quite sure what, because there are many other problems, such as:

Using * where a scalar subquery is expected.
Using '$300' to presumably compare against a number. (Or worse, storing a "price" as a string.)
Having a scalar subquery return multiple rows.

If you want conditional logic, then this is what I think you want:

SELECT p.*
FROM Products p
WHERE p.Color = 'Black' AND
      (p.Item <> 'Dresses' OR p.Price < 300)

answered Nov 24, 2014 at 0:02

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gordon Linoff Over a year ago

@zerkms . . . I rephrased that part.

Chaya Cooper Over a year ago

I'm not clear what I would need to add before the subset in order to get the IF statement to work, so I'm hoping that the information I just added to the question should help clarify what it is that I'm trying to do

Collectives™ on Stack Overflow

Syntax for MySQL SELECT statement with a subquery within the IF statement

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related