Relate column of one table with all other records

Question

Relate column of one table with all other records

Navigation

#1 by (1 votes)

0

I have 2 tables, posts and posts_images , the post table has the fields id , title , category and images , and the posts_images table has: post_id , img_url and etc ..., / strong> of the posts table with all records of the posts_images table that have the same post_id as the id field of the posts

In short, POSTS table

id,
title,
category,
images => todos os registros da tabela 'posts_images' com o post_id = id

How can I do this?

mysql sql

asked by anonymous 05.05.2018 / 23:03

1 answer

CardView within a ScrollView Optimize firebase cloud storage

score 1 · Accepted Answer

Assuming your data structure is something like:

CREATE TABLE tb_post
(
   id BIGINT PRIMARY KEY,
   title TEXT NOT NULL,
   category TEXT NOT NULL
 );

CREATE TABLE tb_post_image
(
  id BIGINT PRIMARY KEY,
  id_post BIGINT NOT NULL,
  url TEXT NOT NULL
);

ALTER TABLE tb_post_image ADD FOREIGN KEY (id_post) REFERENCES tb_post(id);

Containing data similar to this:

-- POST #1
INSERT INTO tb_post ( id, title, category ) VALUES ( 1, 'Garota de Ipanema', 'Musica' );

-- POST #2
INSERT INTO tb_post ( id, title, category ) VALUES ( 2, 'Noite na Taverna', 'Literatura' );
INSERT INTO tb_post_image ( id, id_post, url ) VALUES ( 1, 2, 'noite1.jpg');
INSERT INTO tb_post_image ( id, id_post, url ) VALUES ( 2, 2, 'noite2.jpg');

-- POST #3
INSERT INTO tb_post ( id, title, category ) VALUES ( 3, 'Nao Sei Quantas Almas Tenho', 'Conto' );
INSERT INTO tb_post_image ( id, id_post, url ) VALUES ( 3, 3, 'alma1.jpg');
INSERT INTO tb_post_image ( id, id_post, url ) VALUES ( 4, 3, 'alma2.jpg');

Your query can be done like this:

SELECT
  p.id,
  p.title,
  img.id,
  img.url
FROM
  tb_post AS p
LEFT JOIN
  tb_post_image AS img ON ( img.id_post = p.id )
ORDER BY
  p.id,
  img.id;

Output:

| id |                       title |     id |        url |
|----|-----------------------------|--------|------------|
|  1 |           Garota de Ipanema | (null) |     (null) |
|  2 |            Noite na Taverna |      1 | noite1.jpg |
|  2 |            Noite na Taverna |      2 | noite2.jpg |
|  3 | Nao Sei Quantas Almas Tenho |      3 |  alma1.jpg |
|  3 | Nao Sei Quantas Almas Tenho |      4 |  alma2.jpg |
|  3 | Nao Sei Quantas Almas Tenho |      5 |  alma3.jpg |

SQL Fiddle: link

EDIT

If the intent is to group the image records into an array , you can do a query like:

SELECT
     p.title,
     p.category,
     array_agg(img.url) AS array
FROM
     tb_post AS p
LEFT JOIN
    tb_post_image AS img ON ( img.id_post = p.id )
WHERE
     p.id = 3
GROUP BY
     p.title,
     p.category;

Output:

|                       title | category |                         array |
|-----------------------------|----------|-------------------------------|
| Nao Sei Quantas Almas Tenho |    Conto | alma1.jpg,alma2.jpg,alma3.jpg |

SQL Fiddle: link