perform inner join on same table in mysql

During application development, i was stuck in one mysql – query problem.  i don’t know whether this post title gives meaning or not. but I had used this title to search on google for my problem’s solution.  so i kept same here.

let me describe problem

+-------------------------+---------+
| switch                  | port_no |
+-------------------------+---------+
| 00:00:00:00:00:00:00:02 | 3       |
| 00:00:00:00:00:00:00:01 | 2       |
| 00:00:00:00:00:00:00:01 | 1       |
| 00:00:00:00:00:00:00:02 | 1       |
| 00:00:00:00:00:00:00:04 | 2       |

so i have such type of data in sla table.  and i want bellow output

Expected output

+-------------------------+---------+
| switch                  | port_no |
+-------------------------+---------+
| 00:00:00:00:00:00:00:02 | 3,1     |
| 00:00:00:00:00:00:00:01 | 2,1     |
| 00:00:00:00:00:00:00:04 | 2       |

so here is magic function which saved my life 🙂

Function :

Look at GROUP_CONCAT()

Query which return expected output

select switch,GROUP_CONCAT(port_no) from sla group by switch;

i know lots of newbie developers got such problem so i’m sharing here..
if anyone have best title for it .. plz suggest it in comment.

🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s