Query a specific name within the same table (MySQL)

Question

Table devices:

Name: Router1
Type: NODE
Class: Main

Name: Router1
Type: IF
Class: T1 0/0/1

Name: Router1
Type: IF
Class: Fa 0/1

The above is a minimal example.

The output I desire is:

Router1 | T1 0/0/1, Fa 0/1

Is this possible without creating temp tables, procedure, or a script?

I've thought deeply, but in the real world example there are multiple devices I need their IFs class from. You cannot just pull each device in any way I know.

you condition is to group them by type "IF"?

A.B
– A.B

2015-05-11 09:26:14 +00:00
Commented May 11, 2015 at 9:26 — A.B
– A.B, Commented May 11, 2015 at 9:26

A.B · Accepted Answer · 2015-05-11 09:27:21Z

2

You can use GROUP_CONCAT on class column while group them according to name

SELECT Name, GROUP_CONCAT(class)
FROM devices
GROUP BY Name
WHERE Type= 'IF'
/*WHERE Name = 'Router_1'*/

edited May 11, 2015 at 9:27

answered May 11, 2015 at 9:19

A.B

20.5k3 gold badges43 silver badges74 bronze badges

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

5 Comments

Kirs Kringle Over a year ago

No way to do this for every device in the database? I know you can do it that way, but say there are 100 devices with all different IFs? I tired a sub-query.

A.B Over a year ago

Now this will work for all generllay test it, i have updated the answer @KirsKringle

Kirs Kringle Over a year ago

Man I had that but without GROUP BY and I was racking my brain what is wrong with me. I can't believe I couldn't think of that. Thank you.

A.B Over a year ago

you condition is to group them by type?

A.B Over a year ago

@KirsKringle my pleasure, i have added where clause for 'Type' also, you and upvote and accept it as an answer if it helps :)

Collectives™ on Stack Overflow

Query a specific name within the same table (MySQL)

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related