Blog

MySQL Subquery Example
Posted on July 7, 2015 in MySQL by Matt Jennings

Consider a user_quotes MySQL database with the three tables below:

  • users
  • quotes
  • favorites (one to many relationship with the users and quotes tables)

The favorites table has the columns listed below:

  • id
  • user_id (foreign key that references a users.id column)
  • quote_id (foreign key that references a quotes.id column)

To get all values from a quotes.quote column, EXCLUDING the case where a user has a comment in their favorites (like favorites.user_id = 5), use this query and subquery example:

SELECT quotes.id AS quote_id_num, quotes.quoted_by AS quote_author, quotes.quote AS quote_text, 
users.name AS user_name, users.id AS user_id_num
FROM quotes 
LEFT JOIN users 
ON quotes.user_id = users.id
WHERE quotes.id NOT IN
(SELECT quote_id FROM favorites WHERE user_id = 5)

Leave a Reply