CODEWITHSUNDEEP
mysqlsqljoincorrelated-subquery
aiep
aiep

Reputation: 23

Mysql query two table join with reference table

hi i have two table like this :

TABLE 1:

name | distro1 | distro2 | distro3
----------------------------------
foo  | 001     | 002     | 003

TABLE 2:

id  | distro 
---------------
001 | slackware
002 | redhat
003 | debian

i want get select result like this = 

name | dis1      | dis2   | dis3
----------------------------------
foo  | slackware | redhat | debian

The query needed to create those source tables.

CREATE TABLE IF NOT EXISTS `table1` (
  `name` varchar(30) NOT NULL,
  `distro1` varchar(30) NOT NULL,
  `distro2` varchar(30) NOT NULL,
  `distro3` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `table1` (`name`, `distro1`, `distro2`, `distro3`) VALUES
('foo', '001', '002', '003');

CREATE TABLE IF NOT EXISTS `table2` (
  `id` varchar(30) NOT NULL,
  `distro` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `table2` (`id`, `distro`) VALUES
('001', 'slackware'),
('002', 'readhat'),
('003', 'debian');

Upvotes: 1

Views: 4155

Answers (1)

John Woo
John Woo

Reputation: 263723

you can have something like this:

by using INNeR JOIN, it is assumed that all distro have values and has corresponding match on table 2

SELECT  a.Name, 
        b.distro Distro1, 
        c.distro Distro2, 
        d.distro Distro3
FROM    myTableA a
            INNER JOIN myTableB b
                on a.distro1 = b.id
            INNER JOIN myTableB c
                on a.distro2 = c.id
            INNER JOIN myTableB d
                on a.distro3 = d.id

UPDATE 1

SELECT  a.Name, 
        b.distro Distro1, 
        c.distro Distro2, 
        d.distro Distro3
FROM    myTableA a
            LEFT JOIN myTableB b
                on a.distro1 = b.id
            LEFT JOIN myTableB c
                on a.distro2 = c.id
            LEFT JOIN myTableB d
                on a.distro3 = d.id

Upvotes: 1

Related Questions