How to retrieve data from table with join

Question

I'm using MySQL 5.5. with two tables in it:

DROP TABLE IF EXISTS `events_dictionary`; CREATE TABLE `events_dictionary` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(64) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `events_dictionary` VALUES (1, 'Light'),(2, 'Switch'),(3, 'on'),(4, 'off'); DROP TABLE IF EXISTS `events_log`; CREATE TABLE `events_log` ( `log_id` bigint(20) NOT NULL AUTO_INCREMENT, `event_name_id` int(11) NOT NULL DEFAULT '0', `event_param1` int(11) DEFAULT NULL, `event_value1` int(11) DEFAULT NULL, PRIMARY KEY (`log_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `events_log` VALUES (1, 1, 2, 3),(2, 1, 2, 4);

Table events_dictionary contains names for events_log events names,params and values.

So, my question is - how could i select data from event_log table with columns event_name_id, event_param1, event_value1 mapped to name values from events_dictionary table?

I tried to do this query:

SELECT name, event_param1, event_value1 FROM events_log JOIN events_dictionary ON events_log.event_name_id = events_dictionary.id;

But, in this case i see only event_name_id replaced with values from events_dictionary, like this:

name | event_param1 | event_value1 Light | 1 | 1 Light | 1 | 2

And i want to replace event_param1, and event_value1 with names from events_dictionary too.

Thanks in advance!

Joakim Danielson · Accepted Answer · 2018-03-21 10:05:01Z

You need to join to the events_dictionary multiple times

SELECT a.name, b.name, c.name FROM events_log JOIN events_dictionary a ON events_log.event_name_id = a.id JOIN events_dictionary b ON events_log.event_param1 = b.id JOIN events_dictionary c ON events_log.event_value1 = c.id;

PS
Your example for the event_log isn't that helpful , instead insert the values (1,1,2,3),(2,1,2,4) to turn the switch on and off for the light.
DS

Giorgos Betsos · Accepted Answer · 2018-03-21 09:58:23Z

You can use correlated subqueries:

SELECT name, (SELECT t.name FROM events_dictionary AS t WHERE t.id = event_param1) AS param_name, (SELECT t2.name FROM events_dictionary AS t2 WHERE t2.id = event_value1) AS event_name FROM events_log AS el JOIN events_dictionary AS ed ON el.event_name_id = ed.id;

Demo here

Collectives™ on Stack Overflow

How to retrieve data from table with join

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related