Postgres: Foreign key doesn't match all rows

Question

I am starting to learn databases and I have problem with foreign key.

I have this two tables:

1) table with movies

create table movies (
    id_m  serial primary key,
    name varchar (20) NOT NULL,
    type varchar(20) NOT NULL,
    year smallint NOT NULL,
    availability smallint references availability(id_a) NOT NULL
)

2) table with availability

create table availability(
    id_a serial primary key,
    availability (varchar 20) NOT NULL,
)

Table movies looks like:

Thor Blu-Ray 2012 1
Avangers 2 Blu-ray 2014 2
Gran Torino DVD 2008 1
Titanic DVD 1998 2

Table availability looks like: 1. Available 2. unavailable

When I type this:

SELECT movies.id_n,movies.name,availability.availability 
FROM movies 
full outer join availability on movies.id_n = availability.id_a;

Availability only appears in first two columns:

Thor Available
Avangers 2 Unavailable
Gran Torino
Titanic DVD

Could you please help me how to get availability to next movies in my database?

Andreas · Accepted Answer · 2015-05-14 04:13:37Z

1

Here you go:

SELECT * FROM movies 
join availability on movies.availability = availability.id_a;

Some more details: You joined on the pks (movies.id_n = availability.id_a), in most cases joins make sense along fk relationships (movies.availability = availability.id_a). This is all you missed. The other items are just clean-up.

I removed the unneeded full outer clause. See http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/ for a better understanding of the types of joins.

Finally select * is the equivalent of "select all columns"

edited May 14, 2015 at 4:13

answered May 13, 2015 at 22:27

Andreas

5,1562 gold badges28 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres: Foreign key doesn't match all rows

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related