I am trying to get all result combined from two tables. Currently, I am making two requests to get a table, but I believe there are many good ways to combine the result. I've tried using join with this query

USE test;
SELECT * FROM main INNER JOIN main_meta ON main.id = main_meta.ref WHERE main.id = 1;

but ended up having repeating data like this:

There is one main entry which will have any number of metadata for that reason main post is repeating multiple times now. I wanted to get main data once and multiple metadata in result.

The result I'm looking for is this:

CodePudding user response：

If you don't mind having data column with combined values, then you might want to consider using GROUP_CONCAT(). So a query like this:

SELECT main.id, main.body, main.title, main.time, 
       GROUP_CONCAT(main_meta.data)
    FROM main 
INNER JOIN main_meta 
ON main.id = main_meta.ref 
WHERE main.id = 1
GROUP BY id, body, title, time;

.. will return you result like this:

If you want the data column values to be separated (like in your question edit), then this suggestion (works with MySQL v8 or MariaDB v10.2 and above):

SELECT CASE WHEN rn=1 THEN id ELSE '' END id,
       CASE WHEN rn=1 THEN body ELSE '' END body,
       CASE WHEN rn=1 THEN title ELSE '' END title,
       CASE WHEN rn=1 THEN time ELSE '' END time,
       data
 FROM
(SELECT main.id, main.body, main.title, main.time, main_meta.data, 
       ROW_NUMBER() OVER (PARTITION BY main.id ORDER BY main_meta.id) rn
    FROM main 
INNER JOIN main_meta 
ON main.id = main_meta.ref 
WHERE main.id = 1) v;

Although it is possible, I'm not sure if there's any reason to make it like that unless it's only for viewing purposes. And if the end result is going to be shown on a webpage (e.g. for web report view) then, it's better to do the second option in the application code rather than from the MySQL query.

Anyway, here's a demo fiddle for reference