MYSQL Query select only first record of foreign key

user_18066994 0
Viewed 182

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!

mysql sql
2 Answers

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

user_51935360

You should use join with on clause. This makes your query much more readable.

 SELECT 
    P.id,
    p.title,
    u.username,
    u.avatar,
    GROUP_CONCAT(i.image_name)
 FROM
     projects p
 INNER JOIN users u ON u.id = p.user_id
 LEFT JOIN images i ON i.project_id = p.id
user_6346980
Related