CODEWITHSUNDEEP
mysql
Charles R
Charles R

Reputation: 19089

MYSQL query return empty result

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.

Upvotes: 2

Views: 247

Answers (2)

Mark Byers
Mark Byers

Reputation: 838086

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

Upvotes: 2

Charles R
Charles R

Reputation: 19089

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;

Upvotes: 1

Related Questions