I want to know the way join works,I have 3 tables users, addresses and location, users and addresses tables has data but not in location. When i made a join with addresses from users it return data but when i joined with location it don't return data, my question is why it is nil, because i joined user to address and user to location, so if user has data in address table then it will return that data but not in location so do not return location data in location columns. My schema is
create temporary table users (
id serial,
username VARCHAR(25) NOT NULL,
PRIMARY KEY (id)
);
CREATE temporary TABLE addresses (
id serial,
user_id int NOT NULL,
city VARCHAR(30) NOT NULL,
PRIMARY KEY (id),
CONSTRAINT fk_user_id FOREIGN KEY (user_id) REFERENCES users (id)
);
CREATE temporary TABLE location (
id serial,
user_id int NOT NULL,
state VARCHAR(30) NOT NULL,
PRIMARY KEY (id),
CONSTRAINT fk_user_id FOREIGN KEY (user_id) REFERENCES users (id)
);
insert into users(username) values ('u1'), ('u2'), ('u3'), ('u4')
insert into addresses(user_id, city) values (1, 'c1'), (2,'c2'), (3,'c3')
select *
from users u
inner join addresses a on u.id=a.user_id
inner join location l on u.id=l.user_id;
insert into location(user_id, state) values (3, 's2')
