How to create this query in MYSQL? What Join is it?

Question

i cannot create this kind of join

I have two tables on my MySQL database one is for the identity record and the another one is for , i know how to create joins like inner, left, right joins BUT i do not know how to create a join that will merge them

Answer 1

It's not the join that's different, you need to use group by clause and group_concat() function to produce the expected results in the record field of the output. Whether it is a left or inner join between the 2 tables, that's for you to decide. Based on the sample data an inner join will work just fine:

SELECT a.id_tag, a.Name, group_concat(b.Record) as record
FROM table1 a INNER JOIN table2 b ON a.id_tag = b.id_tag
group by a.id_tag, a.Name

Answer 2

Take a look at

http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html

http://www.w3resource.com/mysql/aggregate-functions-and-grouping/aggregate-functions-and-grouping-group_concat.php

SELECT a.id_tag, a.Name, GROUP_CONCAT(b.Record) as Records FROM tablea a LEFT JOIN tableb b ON a.id_tag = b.id_tag GROUP BY b.Record;

To tidy up the records and make it a bit neater you may want to look at

COALESCE

How to use Coalesce in MySQL

Answer 3

SELECT t1.IDtag,
       t1.Name,
       GROUP_CONCAT(COALESCE(t2.Record, 'no rap sheet'))
FROM Table1 t1
LEFT JOIN Table2 t2
    ON t1.IDtag = t2.IDtag
GROUP BY t1.IDtag