mysql query to join five tables

Question

want to join tables

i have five tables like this

Table-1 named as software

CREATE TABLE IF NOT EXISTS `software` (
  `software_name` varchar(50) NOT NULL,
  `software_version` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `software` (`software_name`, `software_version`) VALUES
('freemap', '1.0'),
('freegps', '1.2');

Table-2 named as cms

CREATE TABLE IF NOT EXISTS `cms` (
  `cms_name` varchar(50) NOT NULL,
  `cms_product` varchar(50) NOT NULL,
  `cms_version` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `cms` (`cms_name`, `cms_product`, `cms_version`) VALUES
('org:freemap:1.0', 'freemap', '1.0'),
('org:freegps:1.0', 'freegps', '1.2');

Table-3 named as cms_to_sve

CREATE TABLE IF NOT EXISTS `cms_to_sve` (
  `cms_id` varchar(50) NOT NULL,
  `sw_vul_id` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `cms_to_sve` (`cms_id`, `sw_vul_id`) VALUES
('org:freemap:1.0', '423'),
('org:freemap:1.0', '424'),
('org:freemap:1.0', '425'),
('org:freemap:1.0', '426'),
('org:freegps:1.2', '940'),
('org:freegps:1.2', '941');

Table-4 named as software_details

CREATE TABLE IF NOT EXISTS `software_details` (
  `sw_id` varchar(50) NOT NULL,
  `sve_id` varchar(50) NOT NULL,
  `score` varchar(50) NOT NULL,
  `ratio` varchar(50) NOT NULL,
  `swe_id` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `software_details` (`sw_id`, `sve_id`, `score`, `ratio`, `swe_id`) VALUES
('423', '2001-1991', '5', 'high', '320'),
('424', '2004-1996', '7.5', 'medium', '460'),
('425', '2008-9001', '8', 'low', '122'),
('426', '2012-0002', '4', 'high', '128'),
('940', '2003-1993', '6', 'medium', '424'),
('941', '2006-1994', '3', 'high', '112');

Table-5 named as swe

CREATE TABLE IF NOT EXISTS `swe` (
  `swe_name` varchar(50) NOT NULL,
  `swe_id` varchar(50) NOT NULL,
  `swe_des` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `swe` (`swe_name`, `swe_id`, `swe_des`) VALUES
('ref software', '320', 'hello'),
('ref complicated', '480', 'hi welcome'),
('ref contact', '122', 'how are you'),
('ref admire', '123', 'who is that'),
('ref super', '424', 'well join us'),
('ref nice', '112', 'cheers');

i want to join these five tables

i have few hints

need to compare table 1 and table 2 (that is table software and table cms)
compare software_name with cms_product and software_version with cms_version
these should relate with cms_name
with second table column cms_name has to join with the third table common column cms_id
where in third table cms_id must be equal to sw_vul_id then join fourth table
now join fourth table from third table using sw_vul_id is equal to sw_id and get the remaining column values
join fifth table and fourth table by swe-id and get the other column values

finally i want to have an output like this

i need query for this .

encryptoferia · Accepted Answer

Just like PeteCon said in his comment , left join can be used, hope this will help.

   select tbl1.software_name, tbl1.software_version, tbl4.sve_id, tbl4.score, tbl4.ratio, tbl5.swe_id, tbl5.swe_name, tbl5.swe_des
     from software tbl1
left join cms tbl2 on tbl1.software_name = tbl2.cms_product and tbl1.software_version = tbl2.cms_version
left join cms_to_sve tbl3 on tbl2.cms_name = tbl3.cms_id
left join software_details tbl4 on tbl3.sw_vul_id = tbl4.sw_id
left join swe tbl5 on tbl4.swe_id = tbl5.swe_id

http://sqlfiddle.com/#!9/3735e7/1

I think some of your sample data is not sufficient to get the result that you want, but in your real database the query should give you something like you want.

mysql query to join five tables

Answers (1)

Related Questions