SQL: Display all the records related to common id

Question

I have this following table

    id |         title
-------+--------------------
    16 | Little Women
    16 | The Tell-Tale Heart
    16 | Practical PostgreSQL
    16 | Dynamic Anatomy
    18 | The Cat in the Hat
    18 | Dune
    20 | A Space Odyssey
    20 | Goodnight Moon
    41 | The Shining
    41 | Programming Python
    41 | Perl Cookbook

I want the output should come like this shown below:

    id |         title
-------+------------------
    16 | Little Women
       | The Tell-Tale Heart
       | Practical PostgreSQL
       | Dynamic Anatomy
    18 | The Cat in the Hat
       | Dune
    20 | A Space Odyssey
       | Goodnight Moon
    41 | The Shining
       | Programming Python
       | Perl Cookbook

Displaying all records that is related with id in the right column using mysql. How can I do this? Please provide me the solution for this I will be very grateful.

you cant do this kind of display on mysql, thats the job of application script. — Abhik Chakraborty, May 28 '14 at 10:09
You better do this in your logic. SQL is not designed for that. — juergen d, May 28 '14 at 10:09
@AbhikChakraborty, juergen : We can't use union joins? Actually I have seen this but I don't know the solution so I was asking — Amuk Saxena, May 28 '14 at 10:12
@AmukSaxena Just because you have seen this done, doesn't mean it should be done. SQL is a language for data, not for prettifying. — gvee, May 28 '14 at 10:19

Sadikhasan · Accepted Answer · 2014-05-28T10:20:09.500

1

Try this

SELECT id,GROUP_CONCAT(title separator '\n') FROM mytable GROUP BY id

edited May 28 '14 at 10:20

answered May 28 '14 at 10:14

Sadikhasan

18,365
21
80
122

Donot post exactly the same answer after 3 minutes – M Khalid Junaid May 28 '14 at 10:15
Because it doesn't answer the question. – gvee May 28 '14 at 10:17
@sadikhasan i haven't down voted but this approach will give results in different format as compare to the expected output of OP – M Khalid Junaid May 28 '14 at 10:18
I have already checked that they give same output as expected output – Sadikhasan May 28 '14 at 10:20
@gvee why it doesn't answer the question? – Sadikhasan May 28 '14 at 10:30
@Sadikhasan after your edit for the newline character, it *does* provide a reasonable answer. However I would strongly advise against this method as you are mixing presentation with data. Not a good idea in my book and not very portable! IF it works for the OP then fine, but I would not recommend this approach. – gvee May 28 '14 at 11:05
@Sadikhasan "...you are mixing presentation with data." – gvee May 28 '14 at 14:00

score 0 · Answer 2 · answered May 28 '14 at 10:18

0

This should be done in your presentation layer e.g. your form, report, webpage, etc.

SELECT id
     , title
FROM   your_table
ORDER
    BY id
     , title

Your presentation code can then decide whether to display the ID each time.

answered May 28 '14 at 10:18

gvee

16,732
35
50

ravikumar · Answer 3 · 2014-05-28T10:26:20.960

-1

try this, you will get the results with comma separated.

SELECT id,GROUP_CONCAT(title) FROM mytable GROUP BY id

edited May 28 '14 at 10:26

answered May 28 '14 at 10:10

ravikumar

893
1
8
12

SQL: Display all the records related to common id

3 Answers3

Linked