CODEWITHSUNDEEP
mysqljoin
Aby Sebastian
Aby Sebastian

Reputation: 71

How can i avoid duplication while joining two tables

i have two tables 

  1. test 1

  2. test 2

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 Query -- 

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

the value ' Kerala ' should not be repeated .

Upvotes: 5

Views: 102

Answers (4)

Bernd Buffen
Bernd Buffen

Reputation: 15057

try this

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;

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

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 produce the required result as long as there is a single record having MIN(name) per jid in test2 table.

Demo here

Upvotes: 1

Sandeep Nambiar
Sandeep Nambiar

Reputation: 1676

you can user Group concat method.Pls check below query

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

Upvotes: 2

grael
grael

Reputation: 655

I don't think it's possible - you can't have empty values inside returned values.

Upvotes: 0

Related Questions