I have two tables Messages
and Files
. The Files
table references Messages
through its message_id
foreign key which corresponds to the Messages
's primary key named message_id
as well. A message entity may or may not have a file but there cannot be a file without a message.
So I want to select everything in one query. All the messages and if there is a file for a message then select a file as well. As far as I understand the resulting query should look something like this:
select * from Messages union select * from Files where message_id = Messages.message_id
unfortunately this simple query is not valid. I also tried using joins:
select * from Messages
left outer join Files on Messages.message_id = Files.message_id
union
select * from Files
left outer join Messages on Messages.message_id = Files.message_id
but it gives me only those messages which have a file. Using subqueries doesn't seem to be a solution. So how do I do this?
CodePudding user response:
You want a full outer join here, which SQLite does not directly support. You may emulate it with a union, along the lines of what you have already tried:
SELECT m.*, f.*
FROM Messages m
LEFT JOIN Files f ON m.message_id = f.message_id
UNION ALL
SELECT m.*, f.*
FROM Files f
LEFT JOIN Messages m ON m.message_id = f.message_id
WHERE m.message_id IS NULL;
CodePudding user response:
The key point of your requirement is:
A message entity may or may not have a file but there cannot be a file without a message
which is the definition of a LEFT
join of Messages
to Files
:
SELECT *
FROM Messages m LEFT JOIN Files f
ON f.message_id = m.message_id;
CodePudding user response:
If you want data from one table and, if it exists, data from another table then why does a simple left outer join not work?
select * from Messages
left outer join Files on
Messages.message_id = Files.message_id