I have the following table as a result of my query :
value1 name
------ ------
abc JOHN
def JOHN
mno JOHN
mno JOHN
abc JAMES
abc JAMES
def JAMES
mno RICK
In the above table the value mno is repeated twice for JOHN and value abc is repeated twice for JAMES.
The query I used is here :
SELECT tc.value, tr.name
FROM table1 tc, table2 tr
WHERE tc.id = tr.roll
ORDER BY tr.name;
The result i would like to expect is that the duplicate values has to be removed from each name. Result should be something like this:
value1 name
------ ------
abc JOHN
def JOHN
mno JOHN
abc JAMES
def JAMES
mno RICK
I Just want to remove the duplicate value from each name. How can I achieve this ?
Try with distinct as below and also please use standard joining as shown.
SELECT DISTINCT tc.value, tr.name
FROM table1 tc
INNER JOIN table2 tr
ON tc.id = tr.roll
ORDER BY tr.name;
You can use DISTINCT keyword as you already tagged :
SELECT DISTINCT t1.value, t2.name
FROM table1 t1
JOIN table2 t2
ON t1.id = t2.roll
ORDER BY t2.name;
or GROUP BY t1.value, t2.name clause, alternatively :
SELECT t1.value, t2.name
FROM table1 t1
JOIN table2 t2
ON t1.id = t2.roll
GROUP BY t1.value, t2.name
ORDER BY t2.name;