CODEWITHSUNDEEP
mysqlsql
NewJ
NewJ

Reputation: 389

How to retrieve data from table with join

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!

Upvotes: 2

Views: 49

Answers (2)

Joakim Danielson
Joakim Danielson

Reputation: 51861

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

Upvotes: 3

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

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

Upvotes: 1

Related Questions