Home > Mobile >  How to select all data from one table and records from another table matching data in first selectio
How to select all data from one table and records from another table matching data in first selectio

Time:04-03

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
  • Related