MySQL Filtering Results from a One-to-many Relationship

I have two tables. quotes_table and flags_table. The quotes_table has a one-to-many relationship with the flags_table – each quote can have multiple flags. flags_table.id_flag is a foreign key of quotes_table.id_flag.

Example Input

quotes_table

pk | quote_key   | id_flag
--------------------------
1  | 111         | 200
2  | 222         | 251
3  | 111         | 250

flags_table

id_flag | name
---------------
200 | testing
251 | converted
250 | paid

Expected Output

quote_key | id_flag | name
-------------------------------------
222       | 251     | converted

In summary, I am trying to return all quotes that do not have a flag of “testing” even if other flags are present.

My current query is still returning quote_key record 111 because of the left join but I am not sure how to “filter” it out.

Current Query

SELECT q.quote_key, q.id_flag, f.name FROM quotes_table AS q
LEFT JOIN flags_table AS f ON q.id_flag = f.id_flag
WHERE  f.name != "testing"

Example Diagram of the quotes_tableand flags_table:

Result of performing a LEFT JOIN between the quotes_table and flags_table.

The rows in red represent a quote, notice the shared quote_key. So I am trying to throw away the entries in red from my results so that I only get quotes without a flag of “testing”.

SQL Fiddle Example

Answer

Hope this helps.

SELECT q.quote_key, q.id_flag, f.name FROM quotes_table AS q
INNER JOIN flags_table AS f ON q.id_flag = f.id_flag
WHERE f.name != "Testing"
AND q.quote_key not in (select quote_key from quotes_table q1, flags_table f1 
where q1.id_flag = f1.id_flag and f1.name = 'Testing')

Advertisement

Answer