Get distinct values mysql

Question

How do I query that I get a distinct clubName based on my query? Below is the website to demo the sql query.

Here is the query used in the SQL fiddle:

SELECT DISTINCT c.ClubName, c.*, p.* 
from Club c inner join Persons p on p.clubName = c.clubName;

Output should be

ManUtd
Barcelona

with 4 rows

I've added your query from SQL fiddle above. The query gets you Club Name and ID plus the associated persons. Your Output is Club names only. So what do you actaully want? Club names only as you say? Then why join with the persons table at all? (And btw: what do you have a club id for, if you don't use it, but use the club name in the persons table instead?) — Thorsten Kettner
– Thorsten Kettner, Commented Oct 17, 2014 at 8:26
what do u mean "with 4rows"? Please post Exact output that u need.. — SHEKHAR SHETE
– SHEKHAR SHETE, Commented Oct 17, 2014 at 8:44

thor · Accepted Answer · 2014-10-17 06:30:21Z

1

If you are only interested in getting distinct clubName's,

SELECT DISTINCT c.ClubName 
FROm Club c inner join Persons p on p.clubName = c.clubName;

This gives you

CLUBNAME
Man Utd
Barcelona

If, however, you include all other columns in the SELECT DISTINCT statement as you did in the OP,

SELECT DISTINCT c.ClubName, c.*, p.* 
from Club c inner join Persons p on p.clubName = c.clubName;

then, there may be multiple DISTINCT rows corresponding to the same clubName, and SQL is correct to give you:

CLUBNAME    ID  LASTNAME    FIRSTNAME
Man Utd     1   Maria   Di
Man Utd     1   Rooney  Wayne
...

answered Oct 17, 2014 at 6:30

thor

22.7k33 gold badges108 silver badges201 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1