Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Selecting primary keys that do not have foreign keys in another table

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
like image 964
SaidbakR Avatar asked Oct 28 '12 11:10

SaidbakR


People also ask

Can you have a primary key without a foreign key?

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.

Is it mandatory for a foreign key to be a primary key in another table?

(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.

Can we join two tables without primary and foreign key?

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.

Can two different tables have the same primary key?

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.


2 Answers

select u.id
from users u
left outer join actions a on a.user_id = u.id
where a.user_id is null
like image 71
juergen d Avatar answered Nov 13 '22 01:11

juergen d


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)
like image 45
ChaosClown Avatar answered Nov 13 '22 01:11

ChaosClown