3

Is it possible to pull more records based on IDs in comma separated list return by GROUP_CONCAT? Is a LEFT JOIN possible here? If so, how can it be done. I am constantly failing at figuring this out, in addition, I've googled a lot already.

I have these two tables:

CREATE Table users (
  id BIGINT(100) AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL, 
  password VARCHAR(50) NOT NULL
);

CREATE Table articles (
  id BIGINT(100) AUTO_INCREMENT PRIMARY KEY,
  author_id BIGINT(100) NOT NULL, 
  title VARCHAR(50) NOT NULL, 
  content TEXT, 
  published DATETIME NOT NULL
);

Insert:

INSERT INTO users (username, password) 
VALUES ('Bob', '5f4dcc3b5aa765d61d8327deb882cf99');

INSERT INTO users (username, password) 
VALUES ('David', '630bf032efe4507f2c57b280995925a9');

INSERT INTO articles (author_id, title, content, published)
VALUES ('1', 'Science 101', 'Science is the concerted human effort to understand, or to understand better, the history of the natural world and how the natural world works, with observable physical evidence as the basis of that understanding1. It is done through observation of natural phenomena, and/or through experimentation that tries to simulate natural processes under controlled conditions.',
        '2015-11-30 09:18:43');

INSERT INTO articles (author_id, title, content, published)
VALUES ('1', 'Health Care', 'Health care or healthcare is the maintenance or improvement of health via the diagnosis, treatment, and prevention of disease, illness, injury, and other physical and mental impairments in human beings.',
        '2016-01-10 15:20:43');

INSERT INTO articles (author_id, title, content, published)
VALUES ('2', 'Physics 101', 'Physics is a natural science based on experiments, measurements and mathematical analysis with the purpose of finding quantitative physical laws for everything from the nanoworld of the microcosmos to the planets, solar systems and galaxies that occupy the macrocosmos.',
        '2016-01-17 14:18:43');

Query:

SELECT 
  articles.id, 
  articles.author_id,
  users.username,
  COUNT(articles.id) AS number_of_articles,
  GROUP_CONCAT(DISTINCT articles.id) AS articles_published
FROM articles 
LEFT JOIN users ON articles.author_id = users.id
GROUP BY articles.author_id 
ORDER BY articles.published DESC;

Result:

id  author_id   username    number_of_articles  articles_published
3   2           David       1                   3
2   1           Bob         2                   2,1

Expected result:

Bob's articles

Health Care
Health care or healthcare is the maintenance or improvement of health via the diagnosis, treatment, and prevention of disease, illness, injury, and other physical and mental impairments in human beings.

Science 101
Science is the concerted human effort to understand, or to understand better, the history of the natural world and how the natural world works, with observable physical evidence as the basis of that understanding1. It is done through observation of natural phenomena, and/or through experimentation that tries to simulate natural processes under controlled conditions.

------

David's articles

Physics 101
Physics is a natural science based on experiments, measurements and mathematical analysis with the purpose of finding quantitative physical laws for everything from the nanoworld of the microcosmos to the planets, solar systems and galaxies that occupy the macrocosmos.

-----

SQLfiddle

1

I think you could use the following query to get the data you need:

SELECT 
  users.username,
  title, content
FROM articles 
LEFT JOIN users ON articles.author_id = users.id
GROUP BY articles.author_id , title
ORDER BY  articles.author_id ,articles.published DESC;

The presentation of it depends on the programming language you will use to display the results.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.