MYSQL Query select only first record of foreign key

First of all, I am pretty new to MySQL. I am currently stuck with the following query. I have several tables that have relations between each other. Every user can create multiple projects. Every project can have one attachment, multiple attachments (images) or no attachment at all. Every project only has one user.

Table users

id	username	avatar
1	user1	image1.png
2	user2	image2.png

Table projects

id	title	user_id
1	project1	1
2	project2	1

Table images

id	image_name	project_id
1	attachment1.png	1
2	attachment2.png	1

My current query:

SELECT 
    projects.id,
    projects.title,
    users.username,
    users.avatar,
    images.image_name
FROM
    users,
    projects,
    images
WHERE
    users.id = user_id
        AND projects.id = project_id

This gives me the following output:

id	title	username	avatar	image_name
1	project1	user1	image1.png	attachment1.png
1	project1	user1	image1.png	attachment2.png

This query only selects projects that are related to an image. Projects that do not have an image are not selected. I would like to select all existing projects. Also, I would like to select only one row per project. Meaning, I would like to either select only the first related image or build up multiple image-columns per project (if this is possbile – otherwise only the first image entry).

Any help is appreciated!

Answer

You should use proper joins so that you can play around with LEFT or RIGHT JOI

So you can use

Also add row_number a window function added in mysql 8 will help selecting the right images

CREATE TABLE users (
  `id` INTEGER,
  `username` VARCHAR(5),
  `avatar` VARCHAR(10)
);

INSERT INTO users
  (`id`, `username`, `avatar`)
VALUES
  ('1', 'user1', 'image1.png'),
  ('2', 'user2', 'image2.png');

CREATE TABLE projects (
  `id` INTEGER,
  `title` VARCHAR(8),
  `user_id` INTEGER
);

INSERT INTO projects
  (`id`, `title`, `user_id`)
VALUES
  ('1', 'project1', '1'),
  ('2', 'project2', '1');

CREATE TABLE images (
  `id` INTEGER,
  `image_name` VARCHAR(15),
  `project_id` INTEGER
);

INSERT INTO images
  (`id`, `image_name`, `project_id`)
VALUES
  ('1', 'attachment1.png', '1'),
  ('2', 'attachment2.png', '1');

    SELECT 
        p.id,
        p.title,
        u.username,
        u.avatar,
        i.image_name
    FROM
        users u INNER JOIN 
        projects p 
        ON u.id = p.user_id
        LEFT JOIN 
        (SELECT `id`, `image_name`, `project_id`, ROW_NUMBER() OVER (PARTITION BY `project_id` ORDER BY `id`) As rn FROM images )  i ON p.id = i.project_id and rn = 1
    WHERE
       u.id = 1

id | title    | username | avatar     | image_name     
-: | :------- | :------- | :--------- | :--------------
 1 | project1 | user1    | image1.png | attachment1.png
 2 | project2 | user1    | image1.png | null           

db<>fiddle here

mysql 5.7 Version

    SELECT 
        p.id,
        p.title,
        u.username,
        u.avatar,
        i.image_name
    FROM
        users u INNER JOIN 
        projects p 
        ON u.id = p.user_id
        LEFT JOIN 
         images   i ON p.id = i.project_id and i.id = (SELECT MIN(`id`) FROM images WHERE project_id = i.project_id)
    WHERE
       u.id = 1

id | title    | username | avatar     | image_name     
-: | :------- | :------- | :--------- | :--------------
 1 | project1 | user1    | image1.png | attachment1.png
 2 | project2 | user1    | image1.png | null           

db<>fiddle here