Database query on 2 tables

Question

I have 2 tables (tag_details and movie_tags) and I want to query those tag_details_id (tag_details) that are NOT part of the movie_tags table. How can I easily get this done?

Does this answer your question? [Find records from one table which don't exist in another](https://stackoverflow.com/questions/367863/find-records-from-one-table-which-dont-exist-in-another) — philipxy, May 20 '23 at 18:50

score 2 · Answer 1 · answered Jan 04 '16 at 21:01

Learn how to use outer joins!

Select * 
from tag_Details TD
LEFT JOIN movie_tags MT
  on TD.Tag_Details_ID = MT.Tag_Details_ID
WHERE MT.Tag_Details_Id is null

or as a right join if you change table order...

Select * 
from movie_tags MT
RIGHT JOIN tag_Details TD
  on TD.Tag_Details_ID = MT.Tag_Details_ID
WHERE MT.Tag_Details_Id is null

This says return all tag_details and related records in movie tags, but only if the movie tag is NULL (which results from missing data, thus what you're after)

score 1 · Accepted Answer · answered Jan 04 '16 at 20:53

1

You can do this easily with a subquery.

Select * from tag_details 
   where tag_details_id not in 
                        (Select tag_details_id from movie_tags)

answered Jan 04 '16 at 20:53

Toby Allen

10,997
11
73
124

thks.. and how does the query change if I want to add to the WHERE clause where (in the movie_tags table) movie_id should be a specific value.. something like Where movie_tags.movie_id = '10' AND tag_details_id not in (Select tag_details_id from movie_tags) – MK69 Jan 04 '16 at 22:26
What happens when you try it. You need to do some research into sql statements, what you are asking is very basic, try a tutorial http://sqlzoo.net/ – Toby Allen Jan 04 '16 at 22:29

score 1 · Answer 3 · edited May 20 '23 at 18:34

1

You have to select all tag_details_Id that are not in the movie_tags table.

This can be achieved with a subquery as follows:

select * 
from tag_details 
where tag_details_Id not in (select tag_details_Id from movie_tags);

edited May 20 '23 at 18:34

forpas

160,666
10
38
76

answered Jan 04 '16 at 20:53

Fabrizio Mazzoni

1,831
2
24
46

score 0 · Answer 4 · answered Jan 04 '16 at 21:01

0

Try this:

SELECT tag_details_id
FROM tag_details
MINUS
SELECT tag_details_id
FROM movie_tags;

answered Jan 04 '16 at 21:01

sayem

171
1
11

2

Please add some explanation to your answer. Code-only answers are generally considered to be of low quality – Tristan Jan 05 '16 at 00:19

Database query on 2 tables

4 Answers4