MySQL Join Returns 1 Null Row

unemployment · January 3, 2012

Can someone please tell me what syntax I am screwing up?

`users`.`id` AS `uid`,
`users`.`username` AS `username`,
`users`.`firstname` AS `firstname`,
`users`.`lastname` AS `lastname`,
`users`.`accounttype` AS `accounttype`,
`users`.`country` AS `country`,
`users`.`state` AS `state`,
`users`.`city` AS `city`,
`markers`.`lat`,
`markers`.`lng`,
`user_privacy`.`avatar` AS `privacy_avatar`,
`user_privacy`.`city` AS `privacy_city`,
`user_privacy`.`location` AS `privacy_location`,
SUM(`investor_info`.`capital_available`) AS `totalavailable`,
`capital`
FROM `users`
LEFT JOIN `investor_info`
ON `users`.`id` = `investor_info`.`uid`
INNER JOIN `assets_countries`
ON `users`.`country` = `assets_countries`.`country_id`
INNER JOIN `assets_states`
ON `users`.`state` = `assets_states`.`state_id`
LEFT JOIN `markers`
ON `users`.`id` = `markers`.`uid`
LEFT JOIN `user_privacy`
ON `users`.`id` = `user_privacy`.`uid`
LEFT OUTER JOIN employees
ON users.id = employees.userid
LEFT OUTER JOIN (
SELECT 
	companies.companyid, 
	SUM(companies.capital) AS `capital`
FROM  `companies`
GROUP BY companies.companytype) SumCompanies
ON employees.companyid = SumCompanies.companyid
WHERE (`assets_countries`.`country` LIKE '%{$country}%'
OR `assets_states`.`state` LIKE '%{$state}%'
OR `users`.`city` LIKE '%{$city}%'
) AND `users`.`status` > 2

fenway · January 3, 2012

If you mix INNER and LEFT JOINs, you might get back nothing at all.

However, if you're getting back any rows, than the columns from the `users` table shouldn't be NULL.

Post your output.

unemployment · January 3, 2012

I want the left outer join the sums the capital to only sum capital for companies that I am employed by. Right now it's summing capital for all companies is my companies table, NOT where my employee ID and company id are found in the employees table.

SELECT
`users`.`id`,
`users`.`firstname`,
`users`.`lastname`,
`users`.`username`,
`users`.`email`,
`users`.`gender`,
`users`.`accounttype`,
`users`.`personalweb`,
`users`.`guestviews`,
`users`.`iviews`,
`users`.`eviews`,
`users`.`credentials`,
`users`.`specialties`,
`users`.`country`,
`users`.`city`,
`users`.`state`,
`users`.`phonenumber`,
`users`.`dateofbirth` AS `dob`,
`users`.`mail_status`,
`users`.`status`,
`markers`.`lat`,
`markers`.`lng`,
`user_privacy`.`avatar` AS `privacy_avatar`,
`user_privacy`.`city` AS `privacy_city`,
`user_privacy`.`location` AS `privacy_location`,
`investor_info`.`investor_type`,
DATE_FORMAT(`users`.`dateofbirth`,'%D') AS `dayofbirth`,
DATE_FORMAT(`users`.`dateofbirth`,'%c') AS `monthofbirth`,
DATE_FORMAT(`users`.`dateofbirth`,'%Y') AS `yearofbirth`,
DATE_FORMAT(`users`.`dateofbirth`,'%D \of %M %Y') AS `dateofbirth`,
DATE_FORMAT(`users`.`signupdate`,'%h:%i %p %M %e, %Y') AS `signupdate`,
SUM(`investor_info`.`capital_available`) AS `totalavailable`,
`companytype`,
`capital`
FROM `users`
LEFT JOIN `investor_info`
ON `users`.`id` = `investor_info`.`uid`
LEFT JOIN `markers`
ON `users`.`id` = `markers`.`uid`
LEFT JOIN `user_privacy`
ON `users`.`id` = `user_privacy`.`uid`
LEFT OUTER JOIN employees
ON users.id = employees.userid
LEFT OUTER JOIN (
SELECT 
	companies.companyid, 
	companies.companytype AS `companytype`,
	SUM(companies.capital) AS `capital`
FROM  `companies`
GROUP BY companies.companytype) SumCompanies
ON employees.companyid = SumCompanies.companyid
WHERE `users`.`status` > 2

Muddy_Funster · January 3, 2012

changing this line :

SUM(`investor_info`.`capital_available`) AS `totalavailable`,

To incorporate a CASE like this:

SUM(CASE WHEN SELECT `check_criteria`THEN SELECT `capital_field.value`ELSE 0 END) AS `totalavailable`,

Should get the result you're after.

fenway · January 4, 2012

And next time, don't start another thread.

unemployment · January 4, 2012

And next time, don't start another thread.

I only started a different thread because I saw them as two separate problems. I'll look into the case statement though I've never used them before.

unemployment · January 4, 2012

changing this line :
SUM(`investor_info`.`capital_available`) AS `totalavailable`,
To incorporate a CASE like this:
SUM(CASE WHEN SELECT `check_criteria`THEN SELECT `capital_field.value`ELSE 0 END) AS `totalavailable`,
Should get the result you're after.

Why would I need to check criteria?

In three tables there are...

Users

user_id

Employees

id user_id company_id

Companies

company_id

I just need to join those so that I only total the amount of capital where the user is employed by that company.

fenway · January 4, 2012

You marked it as "solved", and yet I don't see a solution.

Muddy_Funster · January 4, 2012

changing this line :
SUM(`investor_info`.`capital_available`) AS `totalavailable`,
To incorporate a CASE like this:
SUM(CASE WHEN SELECT `check_criteria`THEN SELECT `capital_field.value`ELSE 0 END) AS `totalavailable`,
Should get the result you're after.
Why would I need to check criteria?

In three tables there are...

Users

user_id

Employees

id user_id company_id

Companies

company_id

I just need to join those so that I only total the amount of capital where the user is employed by that company.

You need the check criteria to tell the SUM not to count every single result returned:

SUM(
CASE WHEN (
  SELECT employee.user_id 
  FROM employees 
    INNER JOIN users 
    ON employees.user_id = users.user_id 
    INNER JOIN companies 
    ON employees.company_id = companies.company_id 
  WHERE company.company_id = "VALUE" AND users.user_id = "VALUE") 
THEN (
  SELECT `capital_field.value`)
ELSE 
  0 
END) 
AS `totalavailable`

should work out the total value stored in the field "capital_field" which has both a matching company ID and user ID to whatever VALUE you choose to use.

unemployment · January 5, 2012

Here is the SQL. I've made some modification though they still aren't quite right. I really do appreciate the help, but I just wish I could get it working. I wasn't sure how to adjust the VALUE in the case statement. Any thoughts?

Here is my error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SUM( CASE WHEN ( SELECT employees.userid FROM employees ' at line 46

SELECT
                `users`.`id`,
                `users`.`firstname`,
                `users`.`lastname`,
                `users`.`username`,
                `users`.`email`,
                `users`.`gender`,
                `users`.`accounttype`,
                `users`.`personalweb`,
                `users`.`guestviews`,
                `users`.`iviews`,
                `users`.`eviews`,
                `users`.`credentials`,
                `users`.`specialties`,
                `users`.`country`,
                `users`.`city`,
                `users`.`state`,
                `users`.`phonenumber`,
                `users`.`dateofbirth` AS `dob`,
			`users`.`mail_status`,
			`users`.`status`,
			`markers`.`lat`,
			`markers`.`lng`,
			`user_privacy`.`avatar` AS `privacy_avatar`,
			`user_privacy`.`city` AS `privacy_city`,
			`user_privacy`.`location` AS `privacy_location`,
                `investor_info`.`investor_type`,
                DATE_FORMAT(`users`.`dateofbirth`,'%D') AS `dayofbirth`,
                DATE_FORMAT(`users`.`dateofbirth`,'%c') AS `monthofbirth`,
                DATE_FORMAT(`users`.`dateofbirth`,'%Y') AS `yearofbirth`,
                DATE_FORMAT(`users`.`dateofbirth`,'%D \of %M %Y') AS `dateofbirth`,
                DATE_FORMAT(`users`.`signupdate`,'%h:%i %p %M %e, %Y') AS `signupdate`,
                SUM(`investor_info`.`capital_available`) AS `totalavailable`,
			`companytype`,
			`capital`
            FROM `users`
            LEFT JOIN `investor_info`
            ON `users`.`id` = `investor_info`.`uid`
		LEFT JOIN `markers`
		ON `users`.`id` = `markers`.`uid`
		LEFT JOIN `user_privacy`
		ON `users`.`id` = `user_privacy`.`uid`
            LEFT OUTER JOIN employees
            ON users.id = employees.userid
            LEFT OUTER JOIN (
			SUM(
			 CASE WHEN (
			  SELECT employees.userid 
			  FROM employees 
				INNER JOIN users 
				ON employees.userid = users.id 
				INNER JOIN companies 
				ON employees.companyid = companies.companyid 
			  WHERE company.companyid = employees.companyid AND users.id = employee.userid) 
			 THEN (
			  SELECT `company.capital`)
			 ELSE 
			  0 
			 END) 
			AS `totalavailable`) SumCompanies
            ON employees.companyid = SumCompanies.companyid
		WHERE `users`.`status` > 2

fenway · January 5, 2012

What on earth is that bizarre case statement doing?

unemployment · January 5, 2012

What on earth is that bizarre case statement doing?

The goal of this is to only sum the column where the

user.id that is linked to employees.userid that is linked to employees.companyid that is linked to companies.companyid will then sum the companies.capital column.

It doesn't work though. You'll see in the first example I was tried to get the sum of capital column. The problem was that it was summing the entire capital column in the companies table and I only want it to sum the capital that is available to the specific employee / user. The user can have multiple companies.

fenway · January 6, 2012

That syntax error is due to a missing "SELECT'

unemployment · January 8, 2012

That syntax error is due to a missing "SELECT'

Where? I don't know what you mean.

fenway · January 8, 2012

Subqueries have to start with SELECT -- not SUM.

unemployment · January 8, 2012

I made some more edits and added in the SELECT, but I still get #1054 - Unknown column 'companies.capital' in 'field list'

I don't understand this because capital is a column in the companies table. Does it need a FROM clause?

SELECT
    `users`.`id`,
    `users`.`firstname`,
    `users`.`lastname`,
    `users`.`username`,
    `users`.`email`,
    `users`.`gender`,
    `users`.`accounttype`,
    `users`.`personalweb`,
    `users`.`guestviews`,
    `users`.`iviews`,
    `users`.`eviews`,
    `users`.`credentials`,
    `users`.`specialties`,
    `users`.`country`,
    `users`.`city`,
    `users`.`state`,
    `users`.`phonenumber`,
    `users`.`dateofbirth` AS `dob`,
`users`.`mail_status`,
`users`.`status`,
`markers`.`lat`,
`markers`.`lng`,
`user_privacy`.`avatar` AS `privacy_avatar`,
`user_privacy`.`city` AS `privacy_city`,
`user_privacy`.`location` AS `privacy_location`,
    `investor_info`.`investor_type`,
    DATE_FORMAT(`users`.`dateofbirth`,'%D') AS `dayofbirth`,
    DATE_FORMAT(`users`.`dateofbirth`,'%c') AS `monthofbirth`,
    DATE_FORMAT(`users`.`dateofbirth`,'%Y') AS `yearofbirth`,
    DATE_FORMAT(`users`.`dateofbirth`,'%D \of %M %Y') AS `dateofbirth`,
    DATE_FORMAT(`users`.`signupdate`,'%h:%i %p %M %e, %Y') AS `signupdate`,
    SUM(`investor_info`.`capital_available`) AS `totalavailable`,
`companytype`,
`capital`
FROM `users`
LEFT JOIN `investor_info`
ON `users`.`id` = `investor_info`.`uid`
LEFT JOIN `markers`
ON `users`.`id` = `markers`.`uid`
LEFT JOIN `user_privacy`
ON `users`.`id` = `user_privacy`.`uid`
LEFT OUTER JOIN employees
ON users.id = employees.userid
LEFT OUTER JOIN (
   SELECT
SUM(
 CASE WHEN (
  SELECT employees.userid 
  FROM employees 
	INNER JOIN users 
	ON employees.userid = users.id 
	INNER JOIN companies 
	ON employees.companyid = companies.companyid 
  WHERE companies.companyid = employees.companyid AND users.id = employees.userid) 
 THEN (
  SELECT companies.capital)
 ELSE 
  0 
 END) 
AS `totalavailable`) SumCompanies
ON employees.companyid = SumCompanies.companyid
WHERE `users`.`status` > 2

Muddy_Funster · January 9, 2012

Yes, you will need to add companies to the FROM clause because the way it stands just now you arn't actualy refferencing it in the main SELECT. You are using it in a sub query, and that subquery is providing a specific result set that the main SELECT refferences using the aliases that you have given it as though it was a table it's self. the main Query does not get direct access to the tables used in the subquery as it is run independant of the main query.

unemployment · January 9, 2012

Yes, you will need to add companies to the FROM clause because the way it stands just now you arn't actualy refferencing it in the main SELECT. You are using it in a sub query, and that subquery is providing a specific result set that the main SELECT refferences using the aliases that you have given it as though it was a table it's self. the main Query does not get direct access to the tables used in the subquery as it is run independant of the main query.

Ok I sort of understand. Now I'm getting an error that says "Subquery returns more than 1 row". Any ideas?

SELECT
    `users`.`id`,
    `users`.`firstname`,
    `users`.`lastname`,
    `users`.`username`,
    `users`.`email`,
    `users`.`gender`,
    `users`.`accounttype`,
    `users`.`personalweb`,
    `users`.`guestviews`,
    `users`.`iviews`,
    `users`.`eviews`,
    `users`.`credentials`,
    `users`.`specialties`,
    `users`.`country`,
    `users`.`city`,
    `users`.`state`,
    `users`.`phonenumber`,
    `users`.`dateofbirth` AS `dob`,
`users`.`mail_status`,
`users`.`status`,
`markers`.`lat`,
`markers`.`lng`,
`user_privacy`.`avatar` AS `privacy_avatar`,
`user_privacy`.`city` AS `privacy_city`,
`user_privacy`.`location` AS `privacy_location`,
    `investor_info`.`investor_type`,
    DATE_FORMAT(`users`.`dateofbirth`,'%D') AS `dayofbirth`,
    DATE_FORMAT(`users`.`dateofbirth`,'%c') AS `monthofbirth`,
    DATE_FORMAT(`users`.`dateofbirth`,'%Y') AS `yearofbirth`,
    DATE_FORMAT(`users`.`dateofbirth`,'%D \of %M %Y') AS `dateofbirth`,
    DATE_FORMAT(`users`.`signupdate`,'%h:%i %p %M %e, %Y') AS `signupdate`,
    SUM(`investor_info`.`capital_available`) AS `totalavailable`,
`companytype`,
`capital`
FROM `users`
LEFT JOIN `investor_info`
ON `users`.`id` = `investor_info`.`uid`
LEFT JOIN `markers`
ON `users`.`id` = `markers`.`uid`
LEFT JOIN `user_privacy`
ON `users`.`id` = `user_privacy`.`uid`
LEFT OUTER JOIN employees
ON users.id = employees.userid
LEFT OUTER JOIN (
   SELECT
SUM(
 CASE WHEN (
  SELECT employees.userid 
  FROM employees 
	INNER JOIN users 
	ON employees.userid = users.id 
	INNER JOIN companies 
	ON employees.companyid = companies.companyid 
  WHERE companies.companyid = employees.companyid AND users.id = employees.userid) 
 THEN (
  SELECT companies.capital
          FROM companies 
         )
 ELSE 
  0 
 END) 
AS `totalavailable`) SumCompanies
ON employees.companyid = SumCompanies.companyid
WHERE `users`.`status` > 2

unemployment · January 9, 2012

I fixed this query. It's working now since I guess all I had to do was change the group by column, but I still want to add in another subquery. Here is the final version so far:

SELECT
`users`.`id`,
`users`.`firstname`,
`users`.`lastname`,
`users`.`username`,
`users`.`email`,
`users`.`gender`,
`users`.`accounttype`,
`users`.`personalweb`,
`users`.`guestviews`,
`users`.`iviews`,
`users`.`eviews`,
`users`.`credentials`,
`users`.`specialties`,
`users`.`country`,
`users`.`city`,
`users`.`state`,
`users`.`phonenumber`,
`users`.`dateofbirth` AS `dob`,
`users`.`mail_status`,
`users`.`status`,
`markers`.`lat`,
`markers`.`lng`,
`user_privacy`.`avatar` AS `privacy_avatar`,
`user_privacy`.`city` AS `privacy_city`,
`user_privacy`.`location` AS `privacy_location`,
`investor_info`.`investor_type`,
DATE_FORMAT(`users`.`dateofbirth`,'%D') AS `dayofbirth`,
DATE_FORMAT(`users`.`dateofbirth`,'%c') AS `monthofbirth`,
DATE_FORMAT(`users`.`dateofbirth`,'%Y') AS `yearofbirth`,
DATE_FORMAT(`users`.`dateofbirth`,'%D \of %M %Y') AS `dateofbirth`,
DATE_FORMAT(`users`.`signupdate`,'%h:%i %p %M %e, %Y') AS `signupdate`,
SUM(`investor_info`.`capital_available`) AS `totalavailable`,
`companytype`,
`capital`
FROM `users`
LEFT JOIN `investor_info`
ON `users`.`id` = `investor_info`.`uid`
LEFT JOIN `markers`
ON `users`.`id` = `markers`.`uid`
LEFT JOIN `user_privacy`
ON `users`.`id` = `user_privacy`.`uid`
LEFT OUTER JOIN employees
ON users.id = employees.userid
LEFT OUTER JOIN (
SELECT 
	companies.companyid, 
	companies.companytype AS `companytype`,
	SUM(companies.capital) AS `capital`
FROM  `companies`
GROUP BY companies.companyid) SumCompanies
ON employees.companyid = SumCompanies.companyidz
WHERE `users`.`status` > 2

I need to add in:

SELECT COUNT(`partner_id`) FROM `partners` WHERE (`user_id` = ${uid} OR `friend_id` = ${uid}) AND `approved` = 1

When I made this query I wrote it to work with the variables specified. I tried adding this in as a subquery, but can't seem to get it to work. I don't want the variables in the subquery. I just want them to group for every user.

fenway · January 9, 2012

We're going to have to start again... I can't follow this any more.

kickstart · January 10, 2012

Hi

Think this is the subquery you want::-

SELECT `user_id` AS PersonId, COUNT(`partner_id`) AS PartnerCount 
FROM `partners` 
WHERE `approved` = 1 
GROUP BY 
UNION
SELECT `friend_id` AS PersonId, COUNT(`partner_id`)  AS PartnerCount 
FROM `partners` 
WHERE `approved` = 1 
GROUP BY `friend_id`) AS SubQuery
GROUP BY PersonId

Trying to integrate that into your main query gives something like:-

SELECT
`users`.`id`,
`users`.`firstname`,
`users`.`lastname`,
`users`.`username`,
`users`.`email`,
`users`.`gender`,
`users`.`accounttype`,
`users`.`personalweb`,
`users`.`guestviews`,
`users`.`iviews`,
`users`.`eviews`,
`users`.`credentials`,
`users`.`specialties`,
`users`.`country`,
`users`.`city`,
`users`.`state`,
`users`.`phonenumber`,
`users`.`dateofbirth` AS `dob`,
`users`.`mail_status`,
`users`.`status`,
`markers`.`lat`,
`markers`.`lng`,
`user_privacy`.`avatar` AS `privacy_avatar`,
`user_privacy`.`city` AS `privacy_city`,
`user_privacy`.`location` AS `privacy_location`,
`investor_info`.`investor_type`,
DATE_FORMAT(`users`.`dateofbirth`,'%D') AS `dayofbirth`,
DATE_FORMAT(`users`.`dateofbirth`,'%c') AS `monthofbirth`,
DATE_FORMAT(`users`.`dateofbirth`,'%Y') AS `yearofbirth`,
DATE_FORMAT(`users`.`dateofbirth`,'%D \of %M %Y') AS `dateofbirth`,
DATE_FORMAT(`users`.`signupdate`,'%h:%i %p %M %e, %Y') AS `signupdate`,
SUM(`investor_info`.`capital_available`) AS `totalavailable`,
`companytype`,
`capital`,
SumPeople.PartnerCount
FROM `users`
LEFT JOIN `investor_info`
ON `users`.`id` = `investor_info`.`uid`
LEFT JOIN `markers`
ON `users`.`id` = `markers`.`uid`
LEFT JOIN `user_privacy`
ON `users`.`id` = `user_privacy`.`uid`
LEFT OUTER JOIN employees
ON users.id = employees.userid
LEFT OUTER JOIN (
SELECT 
	companies.companyid, 
	companies.companytype AS `companytype`,
	SUM(companies.capital) AS `capital`
FROM  `companies`
GROUP BY companies.companyid) SumCompanies
ON employees.companyid = SumCompanies.companyidz
LEFT OUTER JOIN (SELECT PersonId, SUM(PartnerCount) AS PartnerCount
FROM
(SELECT `user_id` AS PersonId, COUNT(`partner_id`) AS PartnerCount 
FROM `partners` 
WHERE `approved` = 1 
GROUP BY 
UNION
SELECT `friend_id` AS PersonId, COUNT(`partner_id`)  AS PartnerCount 
FROM `partners` 
WHERE `approved` = 1 
GROUP BY `friend_id`) AS SubQuery
GROUP BY PersonId) SumPeople
ON users.id = SumPeople.PersonId
WHERE `users`.`status` > 2

All the best

Keith

unemployment · January 10, 2012

KICKSTART! Why are you so good at reading my mind? You're the best. Thanks again.

Sign In

MySQL Join Returns 1 Null Row

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information