Check 2 fields in a select query

Question

I have a mysql table named 'members'.

MEMBERS | member_id, username, email, password, domain_name

email field couldn't be NULL but domain_name could be.

Here I want to make a SELECT Query to get username and email or domain_name. If domain_name exist for a particular member_id then I don't need to select email. If not I need to get email.

This is my query I am using at this time. But It select both email and domain.

SELECT email, username, domain_name 
FROM members
ORDER BY username DESC;

Can anybody help me to update this select query? Thank You.

http://stackoverflow.com/questions/8763310/how-do-write-if-else-statement-in-a-mysql-query — Vinie, Jan 14 '15 at 06:17
I don't know your scenario but I would advise to use logic in your server side code. — Samosa, Jan 14 '15 at 06:23
@Samosa Yes this logic can be use in server side code. But what is different if I use this logic in sql query? — TNK, Jan 14 '15 at 06:40
There are many reasons :- Please read.. http://stackoverflow.com/questions/1704106/business-logic-in-php-or-mysql http://stackoverflow.com/questions/6449072/doing-calculations-in-mysql-vs-php — Samosa, Jan 14 '15 at 15:36

score 3 · Accepted Answer · answered Jan 14 '15 at 06:03

3

You can use CASE

SELECT 
  username,
  CASE
    WHEN domain_name IS NULL 
    THEN email 
    ELSE domain_name 
  END your_alias 
FROM
  members 
ORDER BY username DESC ;

your_alias could be any name you want to specify as a column it can be email or domain_name

answered Jan 14 '15 at 06:03

M Khalid Junaid

63,861
10
90
118

2

People usually have trouble this, so I'll throw out a helper, `your_alias` is actually the name of the column that you will use when you iterate this query. `$row['your_alias']`. – Ohgodwhy Jan 14 '15 at 06:04

Samosa · Answer 2 · 2015-01-14T06:21:19.833

1

You can use the IF function in mysql

SELECT `username`, 
IF(`domain_name` IS NULL ,`email`, `domain_name`) as `email_or_domain` 
FROM `members`

The syntax of the MySQL IF function is:

IF(expr,if_true_expr,if_false_expr)

edited Jan 14 '15 at 06:21

answered Jan 14 '15 at 06:15

Samosa

845
7
19

Check 2 fields in a select query

2 Answers2