issue with a complex Sql join query

Question

I have two table that I need to combine in a specific way. I need the information in one table displayed once but I need the information in the next table to return every matching data it has. here is my example

user

| id | name |
-------------
| 1  | mike |
| 2  | john |
| 3  | bill |
-------------

class

-----------------
| uid | subject | 
-----------------
| 1   | math    |
| 1   | English |
| 2   | math    |
| 3   | math    |
| 3   | English |
-----------------

expected results

--------------------
|name   | mike      |
---------------------
|subject| math      |
|       | English   |
---------------------

or

--------------------
|name   |  subject  |
---------------------
|mike   | math      |
|       | English   |
---------------------

this is my current query

SELECT user.name, class.subject
FROM user
join class on class.uid = user.id
WHERE (user.name like '%mike%')

returns

--------------------
|name   |  subject  |
---------------------
|mike   | math      |
|mike   | English   |
---------------------

but I want to remove all name duplicates. can anyone help with this query

Why do you think it should return a blank for the second line. This is not the standard outcome expected of a join. — Philip Whitehouse
– Philip Whitehouse, Commented Jan 1, 2013 at 0:29
IMO you would be abusing the database to do this work there. You should order by name and do the grouping in your application code. — Dark Falcon
– Dark Falcon, Commented Jan 1, 2013 at 0:29
SQL doesn't really work that way. If you want to group those in your application output, the presentation should be done there. It can be done with a lot of horrifying string manipulation, but that is more appropriately done in the application layer. — Michael Berkowski
– Michael Berkowski, Commented Jan 1, 2013 at 0:29
GROUP_CONCAT function will assemble a comma-separated string of results with a common group. I think it's what you're looking for. (granted you're not really looking for a common result). — d-_-b
– d-_-b, Commented Jan 1, 2013 at 0:40

jenson-button-event · Accepted Answer · 2013-01-01 00:43:28Z

2

You could concatenate the values into a delimited string with a join type function... e.g.

Select u.name, (
      select
      GROUP_CONCAT(c.subject) FROM classes c where u.id = c.uid
    ) As subjects
    From user u

so you end up with

--------------------
|name   |  subjects  |
---------------------
|mike   | math, English |

edited Jan 1, 2013 at 0:43

answered Jan 1, 2013 at 0:34

jenson-button-event

19k13 gold badges99 silver badges165 bronze badges

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

2 Comments

Barmar Over a year ago

Aren't you just reinventing GROUP_CONCAT()?

jenson-button-event Over a year ago

perhaps, just showing the concept, im not big on non-iso mysql stuff

Barmar · Accepted Answer · 2013-01-01 00:36:46Z

1

set @lastname = null;
select case when name = @lastname
            then ""
            else @lastname := name
       end name,
       subject
from (select name, subject
      from user
      join class on id = uid
      where name like '%mike%'
      order by name) x

FIDDLE

However, as others pointed out in the comments, this is usually done in the application code, not in the SQL. In your loop that processes rows, just check if the name is the same as the previous one, and leave that table field blank. Or use a grid widget that automates grouping for you.

edited Jan 1, 2013 at 0:36

answered Jan 1, 2013 at 0:31

Barmar

789k57 gold badges555 silver badges669 bronze badges

Collectives™ on Stack Overflow

issue with a complex Sql join query

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related