MySQL Query with possable empty values on join

Question

I am not very "brilliant" when it comes to more serious MySQL queries than usual. So some help much needed.

I have a tables as follows:

CREATE TABLE IF NOT EXISTS `clients` (
  `c_id` int(11) NOT NULL AUTO_INCREMENT,
  `c_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`c_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

INSERT INTO `clients` (`c_name`) VALUES ('client1');
INSERT INTO `clients` (`c_name`) VALUES ('client2');
INSERT INTO `clients` (`c_name`) VALUES ('client3');
INSERT INTO `clients` (`c_name`) VALUES ('client4');
INSERT INTO `clients` (`c_name`) VALUES ('client5');

CREATE TABLE IF NOT EXISTS `people` (
  `p_id` int(11) NOT NULL AUTO_INCREMENT,
  `p_mark` int(1) NOT NULL DEFAULT '0',
  `p_client_id` int(11) DEFAULT NULL,
  `p_name` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `p_source` varchar(45) COLLATE utf8_unicode_ci NOT NULL DEFAULT '--',
  `p_segment` varchar(45) COLLATE utf8_unicode_ci NOT NULL DEFAULT '--',
  `p_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`p_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

INSERT INTO `people` (`p_mark`,`p_client_id`,`p_name`,`p_source`,`p_segment`,`p_date`) VALUES ('0','1','John','AA','3',NOW());
INSERT INTO `people` (`p_mark`,`p_client_id`,`p_name`,`p_source`,`p_segment`,`p_date`) VALUES ('0','2','Alex','BB','23',NOW());
INSERT INTO `people` (`p_mark`,`p_client_id`,`p_name`,`p_source`,`p_segment`,`p_date`) VALUES ('1','4','Ivan','-','-',NOW());
INSERT INTO `people` (`p_mark`,`p_client_id`,`p_name`,`p_source`,`p_segment`,`p_date`) VALUES ('0','4','Stan','FF','5',NOW());
INSERT INTO `people` (`p_mark`,`p_client_id`,`p_name`,`p_source`,`p_segment`,`p_date`) VALUES ('1','1','Paul','DD','12',NOW());
INSERT INTO `people` (`p_mark`,`p_client_id`,`p_name`,`p_source`,`p_segment`,`p_date`) VALUES ('1','4','Greg','-','-',NOW());
INSERT INTO `people` (`p_mark`,`p_client_id`,`p_name`,`p_source`,`p_segment`,`p_date`) VALUES ('0','1','Eric','EE','7',NOW());
INSERT INTO `people` (`p_mark`,`p_client_id`,`p_name`,`p_source`,`p_segment`,`p_date`) VALUES ('1','5','Thom','BB','92',NOW());
INSERT INTO `people` (`p_mark`,`p_client_id`,`p_name`,`p_source`,`p_segment`,`p_date`) VALUES ('0','4','Finn','BB','41',NOW());
INSERT INTO `people` (`p_mark`,`p_client_id`,`p_name`,`p_source`,`p_segment`,`p_date`) VALUES ('0','2','Leon','CC','21',NOW());
INSERT INTO `people` (`p_mark`,`p_client_id`,`p_name`,`p_source`,`p_segment`,`p_date`) VALUES ('1','2','Sean','AA','25',NOW());

CREATE TABLE IF NOT EXISTS `sources` (
  `src_id` int(11) NOT NULL AUTO_INCREMENT,
  `src_name` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `src_abbr` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `src_client` int(11) NOT NULL,
  PRIMARY KEY (`src_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

INSERT INTO `sources` (`src_name`,`src_abbr`,`src_client`) VALUES ('Aoo oo','AA',1);
INSERT INTO `sources` (`src_name`,`src_abbr`,`src_client`) VALUES ('Boo oo','BB',2);
INSERT INTO `sources` (`src_name`,`src_abbr`,`src_client`) VALUES ('Boo oo','BB',4);
INSERT INTO `sources` (`src_name`,`src_abbr`,`src_client`) VALUES ('Coo oo','CC',1);
INSERT INTO `sources` (`src_name`,`src_abbr`,`src_client`) VALUES ('Doo oo','DD',1);
INSERT INTO `sources` (`src_name`,`src_abbr`,`src_client`) VALUES ('Eoo oo','EE',4);
INSERT INTO `sources` (`src_name`,`src_abbr`,`src_client`) VALUES ('Foo oo','FF',5);
INSERT INTO `sources` (`src_name`,`src_abbr`,`src_client`) VALUES ('Aoo oo','AA',2);
INSERT INTO `sources` (`src_name`,`src_abbr`,`src_client`) VALUES ('Boo oo','BB',5);
INSERT INTO `sources` (`src_name`,`src_abbr`,`src_client`) VALUES ('Coo oo','CC',2);
INSERT INTO `sources` (`src_name`,`src_abbr`,`src_client`) VALUES ('Doo oo','DD',4);
INSERT INTO `sources` (`src_name`,`src_abbr`,`src_client`) VALUES ('Eoo oo','EE',1);
INSERT INTO `sources` (`src_name`,`src_abbr`,`src_client`) VALUES ('Foo oo','FF',4);

CREATE TABLE IF NOT EXISTS `segments` (
  `seg_id` int(11) NOT NULL AUTO_INCREMENT,
  `seg_client_id` int(11) NOT NULL,
  `seg_src_id` int(11) NOT NULL,
  `seg_number` int(11) NOT NULL,
  `seg_value` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`seg_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

INSERT INTO `segments` (`seg_client_id`,`seg_src_id`,`seg_number`,`seg_value`) VALUES ('1','1','3','aa-Seg-c1');
INSERT INTO `segments` (`seg_client_id`,`seg_src_id`,`seg_number`,`seg_value`) VALUES ('0','2','23','bb-Seg-c2');
INSERT INTO `segments` (`seg_client_id`,`seg_src_id`,`seg_number`,`seg_value`) VALUES ('1','4','5','ff-Seg-c4');
INSERT INTO `segments` (`seg_client_id`,`seg_src_id`,`seg_number`,`seg_value`) VALUES ('1','1','12','dd-Seg-c1');
INSERT INTO `segments` (`seg_client_id`,`seg_src_id`,`seg_number`,`seg_value`) VALUES ('1','1','7','ee-Seg-c1');
INSERT INTO `segments` (`seg_client_id`,`seg_src_id`,`seg_number`,`seg_value`) VALUES ('1','5','92','bb-Seg-c5');
INSERT INTO `segments` (`seg_client_id`,`seg_src_id`,`seg_number`,`seg_value`) VALUES ('1','4','41','bb-Seg-c4');
INSERT INTO `segments` (`seg_client_id`,`seg_src_id`,`seg_number`,`seg_value`) VALUES ('1','2','21','cc-Seg-c2');
INSERT INTO `segments` (`seg_client_id`,`seg_src_id`,`seg_number`,`seg_value`) VALUES ('1','2','25','aa-Seg-c2');
INSERT INTO `segments` (`seg_client_id`,`seg_src_id`,`seg_number`,`seg_value`) VALUES ('1','3','35','tt-Seg-c3');
INSERT INTO `segments` (`seg_client_id`,`seg_src_id`,`seg_number`,`seg_value`) VALUES ('1','3','11','rr-Seg-c3');
INSERT INTO `segments` (`seg_client_id`,`seg_src_id`,`seg_number`,`seg_value`) VALUES ('1','5','7','zz-Seg-c5');

I am trying to select all rows from people limited by p_client_id 4 with the following query:

SELECT
  `people`.*
  `clients`.*,
  `sources`.*,
  `segments`.*
FROM
  `people` 
INNER JOIN 
  `clients` ON `people`.`p_client_id` = `clients`.`c_id`
LEFT JOIN 
  `sources` ON `people`.`p_source` = `sources`.`src_abbr`
RIGHT JOIN
  `segments` ON `people`.`p_segment` = `segments`.`seg_number`
WHERE 
  `p_client_id` = '4'
GROUP BY 
  `people`.`p_id`
ORDER BY `people`.`p_date` DESC;

The thing is, that the mentioned query suppose to return 4 rows, but the join fails to return rows where people.p_source and people.p_segment are missing.

Any advice is highly appreciated!

Also prepared sqlfiddle: http://sqlfiddle.com/#!2/0c54c/1

+1 - See!!! This is how you ask an SQL question! With a dataset and the complete query. A fiddle is just a great bonus. — Clockwork-Muse
– Clockwork-Muse, Commented Dec 18, 2012 at 23:01

Barmar · Accepted Answer · 2012-12-18 22:53:46Z

2

Change the join with segments from a RIGHT JOIN to a LEFT JOIN. A RIGHT JOIN includes only the matching rows from the right-hand table of the join (segments).

answered Dec 18, 2012 at 22:53

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL Query with possable empty values on join

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related