Sanjai Kummar Sanjai Kummar - 1 month ago 5
MySQL Question

mysql query to join five tables

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');


enter image description here

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');


enter image description here

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');


enter image description here

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');


enter image description here

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');


enter image description here

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

enter image description here

i need query for this .

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.