How to do this in SQL query?

Question

Table: A                  Table: B                 Table: C
------------            ----------------          -------------
P_id | G_id              P_id  |  Name            G_id | Title
------------            ----------------          -------------
 1   |  1                 1    | john              1   | php
 2   |  1                 2    | jack              2   | sql
 3   |  2                 3    | sam

Now I am quering like:

Select B.name, C.title
from B inner join A on...
inner join c on...

If we input john here then it will display like this:

john php.

But I want to display it like:

john jack  php.

Because G_id of john and jack is same.
How can i do this?

Do you mean output `john` `jack` and `php` on separate rows or as literally `john jack php`? — El Ronnoco, Aug 19 '11 at 10:36
I think he wants a pivot, but I want to see which db before I answer — Bohemian, Aug 19 '11 at 10:38

JK. · Answer 1 · 2011-08-19T11:53:53.077

1

Pseudo code (similar to mysql):

SELECT B.name, C.title 
FROM B
INNER JOIN A ON A.P_id = B.P_id
INNER JOIN C ON A.G_id = C.G_id
WHERE A.G_id = (
                 SELECT A.G_id 
                 FROM B
                 INNER JOIN A ON A.P_id = B.P_id
                 WHERE B.Name LIKE '%John%' LIMIT 1
               );

EDIT:

This will make your results searchable by name, use GROUP_CONCAT and GROUP BY as suggested by Everton Agner to correctly format the results.

edited Aug 19 '11 at 11:53

answered Aug 19 '11 at 10:38

JK.

5,126
1
27
26

+1, it works okay... just add the group_concat and he's good to go – everton Aug 19 '11 at 12:11

everton · Answer 2 · 2011-08-19T12:06:52.347

You need an Aggregation Funcion to work with this kind of grouping. I'm not fluent at MySQL, but try something pretty much like this, using the group_concat() function:

select
    group_concat(b.Name),
    c.Title
from
    A a
    join B b on b.P_id = a.P_id
    join C c on c.G_id = a.G_id
group by
    c.Title

Hopefully it'll show you "john,jack"

Check the docs about Aggregation functions here: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

-- EDIT

Just tested it, it gave me the following output:

+----------------------+-------+
| group_concat(b.Name) | Title |
+----------------------+-------+
| john,jack            | php   |
| sam                  | sql   |
+----------------------+-------+

I hope is that what you want :)

-- EDIT (the last one)

Now I think I understood what you want, just add having group_concat(b.Name) like '%john%' and it'll give you only the groups that john is included... The better choice would be an array contains function, but I haven't found it.

+----------------------+-------+
| group_concat(b.Name) | Title |
+----------------------+-------+
| john,jack            | php   |
+----------------------+-------+

You could probably get better performance with a couple of extra `JOIN`s. Get only the "john" row from table B then `JOIN` to A to get his title(s), then `JOIN` to B again for everyone with the same title(s). Then `JOIN` in A & C for the concatenation and the title name. — Tom H, Aug 19 '11 at 13:21
That could be tested, because essentially, more joins = more processing... But I agree that in terms of design, it would be better. — everton, Aug 19 '11 at 13:41

How to do this in SQL query?

2 Answers2