How can I avoid duplication when joining two tables

Question

I have two tables

1. test 1 2. test 2 

---

The first table has

 **id** - **name** 1 - kerala 2 - Tamilnadu 

---

Second table

  **name** - **jid** value 1 - 1 value 2 - 1 value 3 - 1 value 4 - 1 value 5 - 2 

---

My request is

 SELECT t1.name, t2.name FROM test1 t1 INNER JOIN test2 t2 WHERE t1.id = t2.jid 

---

now i get this result

 **name** - **name** Kerala - value 1 kerala - value 2 kerala - value 3 kerala - value 4 

---

But I need a result like this

 Kerala - value 1 - value 2 - value 3 - value 4 

---

Kerala should not be repeated.

Answer 1

you can use group concat.Pls method below request

  SELECT t1.name,GROUP_CONCAT(t2.name) FROM test1 t1 INNER JOIN test2 t2 WHERE t1.id = t2.jid 

Answer 2

You can use the following query:

 SELECT CASE WHEN t2.name = t3.firstName THEN t1.name ELSE '' END AS name, t2.name FROM test1 t1 INNER JOIN test2 t2 ON t1.id = t2.jid INNER JOIN ( SELECT jid, MIN(name) AS firstName FROM test2 GROUP BY jid) AS t3 ON t2.jid = t3.jid 

This will result in the desired result if the table test2 has one record with MIN(name) per jid .

Demo here

Answer 3

try

 SELECT IF (@oldname = name1,'',name1), name2, @oldname:=name1 AS oldname FROM ( SELECT t1.name AS name1, t2.name AS name2 FROM test1 t1 INNER JOIN test2 t2 WHERE t1.id = t2.jid ) t, (SELECT @oldname:='' ) tmp; 

Answer 4

I do not think this is possible - you cannot have empty values ​​inside the returned values.