MySQL query with conditional statement?

Question

I have a MySQL query left joining two tables. This is the current query result:

id | login | privacy-weight | requires
--------------------------------------------
0  | user  | 1              | NULL
0  | user2 | 1              | NULL
0  | user3 | 1              | privacy-weight

The query itself is not important, as I'd only like to add a WHERE condition to the query as it is now.

I need to fetch only values which (in my own words):

IF (`requires` = 'privacy-weight'), then `privacy-weight` must equal = 0;

That means, I need this condition:

WHERE `privacy-weight` = 0

BUT only if this is true:

requires = 'privacy-weight'

Can this be done?

EDIT

Obviously this is too difficult to understand, therefore, an example output:

privacy-weight | requires
-------------------------
0              | NULL
1              | NULL
0              | privacy-weight
1              | NULL

These would be ignored (not fetched):

privacy-weight | requires
-------------------------
1              | privacy-weight

@Muatik: no, I didn't. that's not the condition I described. — Frantisek
– Frantisek, Commented Jan 30, 2013 at 16:03
what's the point having privacy-weight column then as it's basically based on requires column ? — KennyPowers
– KennyPowers, Commented Jan 30, 2013 at 16:04
This question confuses WHERE clauses and IF statements which are too very different things. What rows do you actually want to output? — Cfreak
– Cfreak, Commented Jan 30, 2013 at 16:14

cheesemacfly · Accepted Answer · 2013-01-30 17:03:34Z

3

You could use this simple test:

WHERE (requires = 'privacy-weight' AND privacy-weight = 0) OR requires <> 'privacy-weight' OR requires IS NULL

The first part (requires = 'privacy-weight' AND privacy-weight = 0) prevents the output of:

privacy-weight | requires
-------------------------
1              | privacy-weight

But keeps:

privacy-weight | requires
-------------------------
0              | privacy-weight

While the second part OR requires <> 'privacy-weight' OR requires IS NULL will keep the following ones:

privacy-weight | requires
-------------------------
0              | NULL
1              | NULL
1              | NULL

edited Jan 30, 2013 at 17:03

answered Jan 30, 2013 at 16:05

cheesemacfly

11.8k11 gold badges57 silver badges72 bronze badges

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

1 Comment

Frantisek Over a year ago

Although not very sophisticated, I think this is the only solution, unfortunately. Accepting for being correct, yielding proper results.

philwinkle · Accepted Answer · 2013-01-30 16:02:27Z

1

I think this should work:

SELECT id, login, ( IF(requires='privacy-weight',0,privacy-weight) ) AS privacy-weight, requires
FROM [mytable]
WHERE [mywhereclause]

For more information on how the IF function works in MySQL see the docs at http://dev.mysql.com/doc/refman/5.5/en/if.html

answered Jan 30, 2013 at 16:02

philwinkle

7,0193 gold badges29 silver badges46 bronze badges

3 Comments

Cfreak Over a year ago

I don't think that's what he wants. That will still output the rows where the requires is some other value

philwinkle Over a year ago

Then he can limit within his where clause. This actually outputs 0 into the column based on the value of another column, which is what he asked for. In my opinion this is more flexible for future reuse of the query.

Cfreak Over a year ago

All he needs to do is limit it in his where clause. There's no reason for an if() statement at all

fancyPants · Accepted Answer · 2013-01-30 16:03:37Z

1

WHERE CASE WHEN requires = 'privacy-weight' THEN 0 ELSE privacy-weight END = 0

answered Jan 30, 2013 at 16:03

fancyPants

52.1k34 gold badges94 silver badges100 bronze badges

3 Comments

Frantisek Over a year ago

Any explanation of those ZEROes, please?

Cfreak Over a year ago

This still outputs the rows that he doesn't want.

Frantisek Over a year ago

Also, this doesn't seem to return values when privacy-weight is NULL

ahoo · Accepted Answer · 2013-01-30 16:46:58Z

1

simply:

WHERE NOT (requires='privacy-weight' AND privacy-weight<>0)

edited Jan 30, 2013 at 16:46

answered Jan 30, 2013 at 16:17

ahoo

1,3812 gold badges17 silver badges38 bronze badges

1 Comment

markus Over a year ago

Try to add an explanation instead of just 'simply'... Why does this solve the problem and in what way.

Cfreak · Accepted Answer · 2013-01-30 16:18:01Z

0

There is an IF() function in mysql as @philwinkle posted but it changes the values of the columns, it doesn't affect the rows. You want to add this to your WHERE statement.

SELECT id, login, `privacy-weight`, requires FROM yourtable 
  WHERE `privacy-weight`=0 AND requires='privacy-weight'

Edit I've changed this back to answer the OP's question as written.

edited Jan 30, 2013 at 16:18

answered Jan 30, 2013 at 16:04

Cfreak

19.4k6 gold badges52 silver badges64 bronze badges

8 Comments

philwinkle Over a year ago

+1 if the intent is to limit the query only to privacy-weights.

Frantisek Over a year ago

Uhm, but I also want values where requires is NULL. This ignores those.

Cfreak Over a year ago

Then you should specify that in your question. I've edited the query to account for null

Frantisek Over a year ago

Still wrong. I want values where privacy-weight is 2. The new query ignores those.

Cfreak Over a year ago

Then your question is wrong. You specifically state you want rows where privacy-weight=0 when requires='privacy-weight'

|

MrYoshiji · Accepted Answer · 2013-01-30 16:39:04Z

0

You can do if condition in where clause

SELECT *
FROM mytable
WHERE IF(requires = 'privacy-weight',privacy-weight = 0,1);

But you can achieve it without if too as you have mentioned you ONLY need

SELECT *
FROM mytable
WHERE requires = 'privacy-weight'
    AND privacy - weight = 0;

edited Jan 30, 2013 at 16:39

MrYoshiji

54.9k13 gold badges130 silver badges120 bronze badges

answered Jan 30, 2013 at 16:07

Muhammad Raheel

19.9k7 gold badges69 silver badges106 bronze badges

Collectives™ on Stack Overflow

MySQL query with conditional statement?

6 Answers 6

1 Comment

3 Comments

3 Comments

1 Comment

8 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

3 Comments

3 Comments

1 Comment

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related