Help with MySQL query

[Darkmatter5]

Here are the tables involved.

CREATE TABLE `jobs` (
  `job_id` int(11) NOT NULL auto_increment,
  `job_number` varchar(20) NOT NULL,
  `client_id` int(11) NOT NULL,
  `county_id` int(11) default NULL,
  `job_desc` longtext,
  `job_loc` longtext,
  `survey_id_1` int(11) default NULL,
  `survey_id_2` int(11) default NULL,
  `survey_id_3` int(11) default NULL,
  `subdivision_id` int(11) default NULL,
  `section` varchar(50) default NULL,
  `lot_blk` varchar( default NULL,
  `fb_pg` varchar(9) default NULL,
  `fnd_date` date default NULL,
  `acerage` varchar(10) default NULL,
  `type_id_1` int(11) default NULL,
  `type_id_2` int(11) default NULL,
  `type_id_3` int(11) default NULL,
  `memo_info` longtext,
  `employee_id` int(11) default NULL,
  `vol_pg` varchar(9) default NULL,
  `estimate` double default NULL,
  `amount` double default NULL,
  `assign_date` date default NULL,
  `completion_date` date default NULL,
  PRIMARY KEY  (`job_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5749 DEFAULT CHARSET=latin1;

CREATE TABLE `surveys` (
  `survey_id` int(11) NOT NULL auto_increment,
  `survey` varchar(50) NOT NULL,
  `county_id` int(11) NOT NULL,
  PRIMARY KEY  (`survey_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4859 DEFAULT CHARSET=latin1;

CREATE TABLE `clients` (
  `client_id` int(11) NOT NULL auto_increment,
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `company_name` varchar(50) default NULL,
  `full_name` varchar(150) default NULL,
  `contact_title` varchar(50) default NULL,
  `address` varchar(30) NOT NULL,
  `city` varchar(15) NOT NULL,
  `state_id` int(11) NOT NULL,
  `zip_code` varchar(10) NOT NULL,
  `home_phone` varchar(12) default NULL,
  `work_phone` varchar(12) default NULL,
  `work_phone_extension` varchar(4) default NULL,
  `fax_phone` varchar(12) default NULL,
  `email` varchar(50) default NULL,
  PRIMARY KEY  (`client_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5391 DEFAULT CHARSET=latin1;

 

Now if I run the following query:

SELECT byrnjobdb.surveys.survey, byrnjobdb.jobs.job_number, byrnjobdb.clients.last_name, byrnjobdb.clients.first_name, byrnjobdb.clients.company_name, byrnjobdb.jobs.job_desc, byrnjobdb.jobs.fb_pg
FROM byrnjobdb.jobs
LEFT JOIN byrnjobdb.surveys
ON byrnjobdb.jobs.survey_id_1=byrnjobdb.surveys.survey_id
LEFT JOIN byrnjobdb.clients
ON byrnjobdb.jobs.client_id=byrnjobdb.clients.client_id
ORDER BY byrnjobdb.surveys.survey ASC

 

Why are the results in the first column "survey" all blank?

 

If am run the following query:

SELECT byrnjobdb.jobs.job_number, byrnjobdb.clients.last_name, byrnjobdb.clients.first_name, byrnjobdb.clients.company_name, byrnjobdb.jobs.job_desc, byrnjobdb.subdivisions.subdivision, byrnjobdb.surveys.survey, byrnjobdb.jobs.lot_blk, byrnjobdb.jobs.fb_pg
FROM byrnjobdb.jobs
LEFT JOIN byrnjobdb.clients
ON byrnjobdb.jobs.client_id=byrnjobdb.clients.client_id
LEFT JOIN byrnjobdb.subdivisions
ON byrnjobdb.jobs.subdivision_id=byrnjobdb.subdivisions.subdivision_id
LEFT JOIN byrnjobdb.surveys
ON byrnjobdb.jobs.survey_id_1=byrnjobdb.surveys.survey_id
ORDER BY byrnjobdb.jobs.job_number ASC, byrnjobdb.clients.last_name ASC, byrnjobdb.clients.first_name ASC, byrnjobdb.clients.company_name

 

I'll get all the results correctly with the survey column all correct. What is wrong with the query in question?

 

Thanks!

[fenway]

It's likely blank because there are no matching rows and you used a left join.