Geek Answers Handbook

Left join does not work when I select an account from another table

I try to join the company and their details, as well as transactions, even if they do not exist.

I am counting transactions to determine how many users are sent to the course, if there are no transactions that I still want to join the company, and details, but the account will be 0, in my query below the training_company table is selected, but Training_details for any the reason is not selected:

SELECT training.*, count(distinct training_transactions.training_transaction_course) as completed_training_payments FROM training LEFT JOIN training_company ON training.course_main = training_company_id LEFT JOIN training_details ON training.course_main = training_details_company LEFT JOIN training_transactions ON training.course_user = training_transactions.training_transaction_user WHERE course_id = ? AND training_transactions.training_transaction_status = 'complete' AND training_transactions.training_transaction_payment_status = 'complete' AND course_enabled = 'enabled'

training_company:

 CREATE TABLE IF NOT EXISTS `training_company` ( `training_company_id` int(11) NOT NULL, `training_company_name` varchar(100) NOT NULL, `training_company_user` int(11) NOT NULL, `training_company_enabled` varchar(50) NOT NULL DEFAULT 'enabled', `training_company_has_avatar` int(5) NOT NULL DEFAULT '0', `training_company_has_banner` int(5) NOT NULL DEFAULT '0' ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1; -- -- Dumping data for table `training_company` -- INSERT INTO `training_company` (`training_company_id`, `training_company_name`, `training_company_user`, `training_company_enabled`, `training_company_has_avatar`, `training_company_has_banner`) VALUES (1, '123', 1, 'enabled', 0, 0),

training_details:

 CREATE TABLE IF NOT EXISTS `training_details` ( `training_details_id` int(11) NOT NULL, `training_details_user` int(11) NOT NULL, `training_details_company` int(11) NOT NULL, `training_details_registration_number` varchar(10) NOT NULL, `training_details_type` varchar(100) NOT NULL, `training_details_name` varchar(100) NOT NULL, `training_details_street` varchar(100) NOT NULL, `training_details_town` varchar(100) NOT NULL, `training_details_county` varchar(100) NOT NULL, `training_details_postcode` varchar(100) NOT NULL, `training_details_country` varchar(100) NOT NULL, `training_details_company_name` varchar(100) NOT NULL, `training_details_company_street` varchar(100) NOT NULL, `training_details_company_town` varchar(100) NOT NULL, `training_details_company_county` varchar(100) NOT NULL, `training_details_company_postcode` varchar(100) NOT NULL, `training_details_company_country` varchar(100) NOT NULL, `training_details_total_employees` varchar(100) NOT NULL, `training_details_fax` varchar(100) NOT NULL, `training_details_landline` varchar(100) NOT NULL, `training_details_mobile` varchar(50) NOT NULL, `training_details_email` varchar(50) NOT NULL, `training_details_website` varchar(250) NOT NULL, `company_differs_address` int(11) NOT NULL DEFAULT '0' ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1; -- -- Dumping data for table `training_details` -- INSERT INTO `training_details` (`training_details_id`, `training_details_user`, `training_details_company`, `training_details_registration_number`, `training_details_type`, `training_details_name`, `training_details_street`, `training_details_town`, `training_details_county`, `training_details_postcode`, `training_details_country`, `training_details_company_name`, `training_details_company_street`, `training_details_company_town`, `training_details_company_county`, `training_details_company_postcode`, `training_details_company_country`, `training_details_total_employees`, `training_details_fax`, `training_details_landline`, `training_details_mobile`, `training_details_email`, `training_details_website`, `company_differs_address`) VALUES (1, 0, 1, '0', '', '123', '123', '123', '123456', 'WN8', 'Australia', '123', '123', '123', '', 'WN8', 'Australia', '', '', '', '', '', '', 4), ,` training_details_company_county`, `training_details_company_postcode`,` training_details_company_country`, `training_details_total_employees`,` training_details_fax`, `training_details_landline`,` training_details_mobile`, `training_details_email`,` training_details_website`, `company_differs_address CREATE TABLE IF NOT EXISTS `training_details` ( `training_details_id` int(11) NOT NULL, `training_details_user` int(11) NOT NULL, `training_details_company` int(11) NOT NULL, `training_details_registration_number` varchar(10) NOT NULL, `training_details_type` varchar(100) NOT NULL, `training_details_name` varchar(100) NOT NULL, `training_details_street` varchar(100) NOT NULL, `training_details_town` varchar(100) NOT NULL, `training_details_county` varchar(100) NOT NULL, `training_details_postcode` varchar(100) NOT NULL, `training_details_country` varchar(100) NOT NULL, `training_details_company_name` varchar(100) NOT NULL, `training_details_company_street` varchar(100) NOT NULL, `training_details_company_town` varchar(100) NOT NULL, `training_details_company_county` varchar(100) NOT NULL, `training_details_company_postcode` varchar(100) NOT NULL, `training_details_company_country` varchar(100) NOT NULL, `training_details_total_employees` varchar(100) NOT NULL, `training_details_fax` varchar(100) NOT NULL, `training_details_landline` varchar(100) NOT NULL, `training_details_mobile` varchar(50) NOT NULL, `training_details_email` varchar(50) NOT NULL, `training_details_website` varchar(250) NOT NULL, `company_differs_address` int(11) NOT NULL DEFAULT '0' ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1; -- -- Dumping data for table `training_details` -- INSERT INTO `training_details` (`training_details_id`, `training_details_user`, `training_details_company`, `training_details_registration_number`, `training_details_type`, `training_details_name`, `training_details_street`, `training_details_town`, `training_details_county`, `training_details_postcode`, `training_details_country`, `training_details_company_name`, `training_details_company_street`, `training_details_company_town`, `training_details_company_county`, `training_details_company_postcode`, `training_details_company_country`, `training_details_total_employees`, `training_details_fax`, `training_details_landline`, `training_details_mobile`, `training_details_email`, `training_details_website`, `company_differs_address`) VALUES (1, 0, 1, '0', '', '123', '123', '123', '123456', 'WN8', 'Australia', '123', '123', '123', '', 'WN8', 'Australia', '', '', '', '', '', '', 4),

Training:

 CREATE TABLE IF NOT EXISTS `training` ( `course_id` int(11) NOT NULL, `course_user` int(11) NOT NULL, `course_main` int(11) NOT NULL, `course_type` varchar(255) NOT NULL, `course_name` varchar(255) NOT NULL, `course_description` text NOT NULL, `course_location` varchar(255) NOT NULL, `course_duration` varchar(255) NOT NULL, `course_fitness_type` varchar(255) NOT NULL, `course_instructor_name` varchar(255) NOT NULL, `course_price` int(15) NOT NULL, `course_start_date` date NOT NULL, `course_max_attendees` int(8) NOT NULL, `course_accommodation` varchar(255) NOT NULL, `course_accommodation_price` varchar(255) NOT NULL, `course_status` varchar(50) NOT NULL, `course_enabled` varchar(10) NOT NULL DEFAULT 'enabled', `course_location_name` varchar(255) NOT NULL, `course_location_street` varchar(255) NOT NULL, `course_location_town` varchar(255) NOT NULL, `course_location_county` varchar(255) NOT NULL, `course_location_postcode` varchar(255) NOT NULL, `course_location_country` varchar(255) NOT NULL, `course_certificate` varchar(250) NOT NULL, `course_certificate_valid` int(30) NOT NULL ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1; -- -- Dumping data for table `training` -- INSERT INTO `training` (`course_id`, `course_user`, `course_main`, `course_type`, `course_name`, `course_description`, `course_location`, `course_duration`, `course_fitness_type`, `course_instructor_name`, `course_price`, `course_start_date`, `course_max_attendees`, `course_accommodation`, `course_accommodation_price`, `course_status`, `course_enabled`, `course_location_name`, `course_location_street`, `course_location_town`, `course_location_county`, `course_location_postcode`, `course_location_country`, `course_certificate`, `course_certificate_valid`) VALUES (1, 3, 1, 'Hazardous', '123', 'dddddddddddddd', 'other', '14', 'lol', 'lol', 123, '2015-11-09', 4, '0', '', 'pending', 'enabled', '123', '123', '123', '123456', '123', 'Australia', '123', 2),

So, how can I get my part in the work on the details, because at the moment he is not joining, although everything is consistent

+7
sql mysql
source share
4 answers

As Gordon Linoff suggested in the first comment, move the training_transactions conditions from the WHERE to the LEFT JOIN .

Of course, you should also explicitly specify columns instead of * and add the appropriate GROUP BY for COUNT to work.

 SELECT training.course_id ,training.course_user ... ,count(distinct training_transactions.training_transaction_course) as completed_training_payments FROM training LEFT JOIN training_company ON training.course_main = training_company_id LEFT JOIN training_details ON training.course_main = training_details_company LEFT JOIN training_transactions ON training_transactions.training_transaction_user = training.course_user AND training_transactions.training_transaction_status = 'complete' AND training_transactions.training_transaction_payment_status = 'complete' WHERE training.course_id = ? AND training.course_enabled = 'enabled' GROUP BY training.course_id ,training.course_user ...

There are also a few things you should look for in this query.

 WHERE course_id = ? AND course_enabled = 'enabled'

It is not clear that the new person is reading the request (since everyone is reading this question and the next person who will support your code, which may be available to you in two years), which table these fields belong to. Always try to explicitly specify a table, for example:

 WHERE training.course_id = ? AND training.course_enabled = 'enabled'

It also helps to use aliases.

Similarly, it is unclear what is happening in the JOIN :

  LEFT JOIN training_company ON training.course_main = training_company_id LEFT JOIN training_details ON training.course_main = training_details_company

Is this just an oversight when simplifying the query on this, or is this your real code? In either case, provide the table name (or alias).

 LEFT JOIN training_company ON training.course_main = training_company.training_company_id LEFT JOIN training_details ON training.course_main = training_details.training_details_company
+5
source share

I would suggest taking a completely different path and not using left connections. Since you want all the data from the training table and you want the aggregate (account in your case) from another table, I would suggest splitting the aggregate into a separate subquery. Here's what I mean (note that I added aliases to your tables to simplify them):

 SELECT t.*, (SELECT count(distinct tt.training_transaction_course) FROM training_company tc JOIN training_details td ON td.course_main = tc.training_details_company JOIN training_transactions tt ON t.course_user = tt.training_transaction_user WHERE t.course_main = tc.training_company_id AND tt.training_transaction_status = 'complete' AND tt.training_transaction_payment_status = 'complete') as completed_training_payments FROM training t WHERE t.course_id = ? AND t.course_enabled = 'enabled'

This ensures that the row returned for each row in the training table matches the entered course_id. Also, if the subquery does not return rows, it returns 0.

+1
source share

There are several issues with this. For starters, it’s hard to play without training_transactions .

Next, from your description and diagram, it will be shown that you want an internal join in the first table. training will always have a company if I read this right. training_details , apparently, are not related to the request and will give a cross product if they were not completely excluded.

I assume that user joining transactions is wrong, since you have training_transaction_course, which I assume is the correct foreign key.

An account is simply wrong - this is not how it works, and you need a GROUP BY clause if you want a count.

The WHERE clause appears right to me, because the elements are not used in any connection.

This will give you something like this:

 SELECT training.course_id, -- everything you put here needs to go in the group by clause count(training_transactions.training_transaction_course) as completed_training_payments FROM training JOIN training_company ON training.course_main = training_company_id LEFT JOIN training_transactions ON training.course_id = training_transactions.training_transaction_course WHERE course_id = ? AND training_transactions.training_transaction_status = 'complete' AND training_transactions.training_transaction_payment_status = 'complete' AND course_enabled = 'enabled' GROUP BY training.course_id, training_transactions.training_transaction_course

~

0
source share

The problem is that your query will never result in multiple lines.

Use Groupby or subqueries as said in other answers.

0
source share

More articles:


All Articles