You are here

Home » Blogs » d.cochran's blog

Return only the difference from MySQL results

published by d.cochran on Mon, 01/14/2013 - 09:07

The below example shows how to return the difference in tables from a MySQL query

Problem: A user has the ability to assign labels to records. When a user is to add a new label to a record only query the labels the user has access to and don't display the labels already assigned.

//selects all available labels to user '1'
SELECT
  labels.label_id, labels.label
FROM labels
LEFT JOIN labels_users USING (label_id)
WHERE (labels.entry_user = '1' || labels_users.user_id = '1')
 
//returns
// label_id: 1, label: Test Label
// label_id: 2, label: Testing Number 2
// label_id: 3, label: Third Label
//selects currently assigned labels to record
SELECT
  labels.label_id, labels.label
FROM labels
LEFT JOIN labels_records USING (label_id)
WHERE labels_records.record_id = '99'
//returns
// label_id: 1, label: Test Label
// label_id: 3, label: Third Label

Solution: Use SELECT DISTINCT to limit our results from two subqueries.

SELECT DISTINCT availableLabels.label_id, availableLabels.label
FROM (
  SELECT
    labels.label_id, labels.label
  FROM labels
  LEFT JOIN labels_users USING (label_id)
  WHERE (labels.entry_user = '1' || labels_users.user_id = '1')
) AS availableLabels
LEFT JOIN (
  SELECT
    labels.label_id
  FROM labels
  LEFT JOIN labels_records USING (label_id)
  WHERE labels_records.record_id = '99'
) AS assignedLabels ON availableLabels.label_id = assignedLabels.label_id
WHERE assignedLabels.label_id IS NULL
ORDER BY availableLabels.label ASC
 
//returns
// label_id: 2, label: Testing Number 2

The reason we use "WHERE assignedLabels.label_id IS NULL" is because when we "LEFT JOIN" our assignedLabels to the availableLabels, we want the results that are not being used.

Tags: