How can I SELECT columns from one table that corresponds with a column of another table on JOIN without creating a relation between the tables?

Question

I have 3 tables:

CREATE TABLE catalog.shoes (
    shoe_id SERIAL PRIMARY KEY,
    brand VARCHAR(255) NOT NULL,
    model VARCHAR(255) NOT NULL,
    price INT NOT NULL,
    CONSTRAINT shoe_name UNIQUE (brand, model)
);

CREATE TABLE catalog.stock (
    item_id SERIAL PRIMARY KEY,
    shoe_id INT REFERENCES catalog.shoes(shoe_id) ON DELETE CASCADE,
    color VARCHAR(255) NOT NULL,
    size INT NOT NULL,
    stock_count INT NOT NULL
);

CREATE TABLE catalog.photos (
    shoe_id INT REFERENCES catalog.shoes(shoe_id) ON DELETE CASCADE,
    color VARCHAR(255) NOT NULL,
    photo_url TEXT UNIQUE
);

I have this query to SELECT rows:

SELECT item_id, st.color st_col, p.color p_col, size, stock_count, brand, model, photo_url
FROM catalog.stock st
JOIN catalog.shoes s ON s.shoe_id = st.shoe_id
JOIN catalog.photos p ON p.shoe_id = st.shoe_id

The issue I have is that color from the photos table (p_col) does not correspond with the color from the stock table (st_col). Is there a simple solution which would match those rows with one another on the SELECT query or would it be better to create the relationship when I CREATE the tables? The reason I do not want to refactor my tables too much (unless it is necessary) is because I have struggled to create a relation between the colors in the table definitions because the color is not unique in the photos table. I assumed it would be easier to make them line up when I retrieve the data instead.

I'm open to changing my tables, but as you can see, I don't like having too complicated definitions since this is just for a school project. I tried creating a CONSTRAINT in the stock table CONSTRAINT (shoe_id, color) UNIQUE and the photos table FOREIGN KEY (shoe_id, color) REFERENCES catalog.stock (shoe_id, color) but I kept getting syntax errors (I tried different ways, such as giving the constraint a name and referencing that name, etc.).

This is an example of returned rows:

item_id st_col  p_col   size    stock_count brand   model           photo_url
1000    Black   White   6       11          Adidas  Breaknet 2.0    https://i.ibb.co/JnxgLWL/adidas-breaknet2-whitebluered.webp
1000    Black   Black   6       11          Adidas  Breaknet 2.0    https://i.ibb.co/3S1WwKj/adidas-breaknet2-blackwhite.webp
1001    Black   White   7       12          Adidas  Breaknet 2.0    https://i.ibb.co/JnxgLWL/adidas-breaknet2-whitebluered.webp
1001    Black   Black   7       12          Adidas  Breaknet 2.0    https://i.ibb.co/3S1WwKj/adidas-breaknet2-blackwhite.webp
1003    White   White   6       5           Adidas  Breaknet 2.0    https://i.ibb.co/JnxgLWL/adidas-breaknet2-whitebluered.webp
1003    White   Black   6       5           Adidas  Breaknet 2.0    https://i.ibb.co/3S1WwKj/adidas-breaknet2-blackwhite.webp

Here we can see that I'm getting a row for each p_col value in the photos table, but I only want the p_col from the corresponding st_col. I should only have one row per item_id.

This is what I expect:

item_id st_col  p_col   size    stock_count brand   model           photo_url
1000    Black   Black   6       11          Adidas  Breaknet 2.0    https://i.ibb.co/3S1WwKj/adidas-breaknet2-blackwhite.webp
1001    Black   Black   7       12          Adidas  Breaknet 2.0    https://i.ibb.co/3S1WwKj/adidas-breaknet2-blackwhite.webp
1003    White   White   6       5           Adidas  Breaknet 2.0    https://i.ibb.co/JnxgLWL/adidas-breaknet2-whitebluered.webp

Answer 1

I simply had to add another condition (AND p.color = st.color) on JOIN catalog.photos, like so:

SELECT item_id, st.color st_col, p.color p_col, size, stock_count, brand, model, photo_url
FROM catalog.stock st
JOIN catalog.shoes s ON s.shoe_id = st.shoe_id
JOIN catalog.photos p ON p.shoe_id = st.shoe_id AND p.color = st.color

Thank you @laurenz-albe