CODEWITHSUNDEEP
sqloracle-database
Mythix
Mythix

Reputation: 11

SQL Join Two Tables Remove Duplicates

Okay I've been searching through all previously asked questions and I'm still having trouble.

I have two tables

Table MOVIES

mov_id
mov_name
actor_id

Table ACTOR

actor_id_2
actor_name

I want to join both tables into one table where it lists

  1. mov_id
  2. mov_name
  3. actor_name

I'm trying to use JOIN WHERE actor_id = actor_id_2 except my code includes the duplicates of repeating the same actor_name 3 times.

Example

I've tried multiple ways of doing this, each way unsuccessful. I'm not sure which way would be the proper way to do this.

Upvotes: 0

Views: 2462

Answers (2)

ARr0w
ARr0w

Reputation: 1731

I'll try to build up your concept.

What you are applying is relational database using primary and foreign keys.

Whenever you have such scenario as above that you need to retrieve data from more than one table and they are related then joins are used.

Joins have many kinds, you can get their details and guidance in this tutorial

Now comes to your tables and data.

Select M.mov_id, M.mov_name, M.actor_name from Movies M
inner join Actor A on M.actor_id = A.actor_id_2

notice: after on keyword you can place multiple conditions for the join to get the most specific data as per your requirement.

Upvotes: 1

Jinesh Shah
Jinesh Shah

Reputation: 952

You can use Inner join which will join tables such that it selects records that have matching values in both tables, write your query as 

select distinct t1.mov_id, t1.mov_name, t2.actor_name from MOVIES t1 inner join ACTOR t2 on t1.actor_id=t2.actor_id_2;

Upvotes: 3

Related Questions