For simplification, I have two tables related with one to many using a foreign key, for example:
Users table:
id
name
Actions table:
id
user_id
one user may have many actions or not. I need an sql select that returns users ids that don't have a user_id value in the actions table.
Users Table:
id name
1 John
2 Smith
3 Alice
Actions Table:
id user_id
1 3
2 1
So I need an sql query that returns the user id 2 (Smith) because the foreign key values don't include the id 2
I tried the following SQL, but it returns all users ids:
SELECT users.id from users left join actions on actions.user_id is null
Primary keys always need to be unique, foreign keys need to allow non-unique values if the table is a one-to-many relationship. It is perfectly fine to use a foreign key as the primary key if the table is connected by a one-to-one relationship, not a one-to-many relationship.
(The data foreign key column must exist in the primary key column in the first table.) Non-Identifying One-To-One — means that the column dragged to a second table will not be added to its primary key, and that the relationship is mandatory and unique.
A primary key is not required. A foreign key is not required either. You can construct a query joining two tables on any column you wish as long as the datatypes either match or are converted to match. No relationship needs to explicitly exist.
Yes. You can have same column name as primary key in multiple tables. Column names should be unique within a table. A table can have only one primary key, as it defines the Entity integrity.
select u.id
from users u
left outer join actions a on a.user_id = u.id
where a.user_id is null
Optimized version would be:
SELECT u.id
FROM users u
LEFT JOIN actions a
ON a.user_id = u.id
AND ISNULL(a.user_id)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With