MYSQL query return empty result

Question

I need to look up email preferences for users.

This table contains the types of email a user can receive, broken down by category.

email_preferences_categories

+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name     | text             | YES  |     | NULL    |                |
| overview | text             | YES  |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+

This table contains their preference for receiving various types. If they haven't set their preferences, this table won't have any rows for them.

email_preferences

+------------+---------------------------------+------+-----+---------+----------------+
| Field      | Type                            | Null | Key | Default | Extra          |
+------------+---------------------------------+------+-----+---------+----------------+
| id         | int(10) unsigned                | NO   | PRI | NULL    | auto_increment |
| user_id    | int(10) unsigned                | NO   |     | NULL    |                |
| name       | text                            | YES  |     | NULL    |                |
| frequency  | enum('Daily','Monthly','None')  | YES  |     | Daily   |                |
+------------+---------------------------------+------+-----+---------+----------------+

I need to construct a MYSQL query that returns the name and frequency corresponding to the email preferences for a given user.

SELECT name, frequency
FROM email_preferences
LEFT JOIN email_preferences_categories using (name)
WHERE user_id = 42

Where I'm having trouble: If the user hasn't set their preferences, this query doesn't return any rows. I would like it to return the default of 'Daily' for email categories that are missing.

where do you need email_preferences_categories (field name is both in emaial_preferences and in emaail_preferences_category) ? Don't you want to get values from user table first ? — Raphaël Althaus
– Raphaël Althaus, Commented Oct 29, 2012 at 15:33
I need email_preferences_categories when the user table is empty or missing an entry. — Charles R
– Charles R, Commented Oct 29, 2012 at 15:55

Mark Byers · Accepted Answer · 2012-10-29 17:52:39Z

2

Change LEFT JOIN to RIGHT JOIN.

...
FROM email_preferences
RIGHT JOIN email_preferences_categories
...

Or alternatively you can swap the tables around:

...
FROM email_preferences_categories
LEFT JOIN email_preferences
...

These two options both do the same thing - ensure that you get all rows from email_preferences_categories even if there is no matching row in email_preferences.

You also need to change the join condition as you already noticed.

I would like it to return the default of 'Daily' for email categories that are missing.

You can use IFNULL:

SELECT name, IFNULL(frequency, 'Daily') AS frequency

edited Oct 29, 2012 at 17:52

answered Oct 29, 2012 at 15:09

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

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

Comments

Charles R · Accepted Answer · 2012-10-29 16:02:05Z

1

This query doesn't need a WHERE clause. It needs a more restrictive JOIN. Here is the full query combined with Mark Byers answer above.

SELECT email_preferences_categories.name, IFNULL(frequency, 'Daily') AS frequency
FROM email_preferences_categories
LEFT JOIN email_preferences
    ON email_preferences.name = email_preferences_categories.name
    AND user_id = 42;

answered Oct 29, 2012 at 16:02

Charles R

19.2k6 gold badges22 silver badges18 bronze badges

Collectives™ on Stack Overflow

MYSQL query return empty result

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related