CODEWITHSUNDEEP
mysql
ATLChris
ATLChris

Reputation: 3296

How to figure out if no association with other tables

I have 3 MySQL tables: tags, place_tags, event_tags

tags:

---------------------
|  tag_id  |  name  |
---------------------
|    1     |  cat   |
---------------------
|    2     |  dog   |
---------------------
|    3     |  fish  |
---------------------

place_tags:

-------------------------
|  place_id  |  tag_id  |
-------------------------
|      1     |     1    |
-------------------------
|      2     |     2    |
-------------------------
|      3     |     1    |
-------------------------

event_tags:

-------------------------
|  event_id  |  tag_id  |
-------------------------
|      1     |     1    |
-------------------------
|      2     |     2    |
-------------------------
|      3     |     1    |
-------------------------

I am trying to write a query that will look at all the tags in the tags table and will find which of them are not associated with either of the other tables. In this example, you can see that tag_id 3 is not used in any other table, so I want to select it and output it.

Upvotes: 1

Views: 173

Answers (2)

rid
rid

Reputation: 63482

An efficient, index-using, single-SELECT, JOIN-based query could be:

   SELECT tags.*
     FROM tags
LEFT JOIN place_tags ON place_tags.tag_id = tags.tag_id
LEFT JOIN event_tags ON event_tags.tag_id = tags.tag_id
    WHERE place_tags.tag_id IS NULL AND
          event_tags.tag_id IS NULL

This joins the tables you're looking into and only selects the tags that have no correspondence in either of them.

Upvotes: 2

Bohemian
Bohemian

Reputation: 425073

select * from tag
where not exists (select * from place_tags where tag_id = tag.tag_id)
and not exists (select * from event_tags where tag_id = tag.tag_id)

Or more efficient, but perhaps harder to read:

select * from tag
where tag_id not in (
    select tag_id from place_tags where tag_id is not null
    union
    select tag_id from event_tags where tag_id is not null)

Note: the where tag_id is not null is needed because if one of the rows has a tag_id of null, the in will always be false.

Upvotes: 1

Related Questions