Union Won't Merge SQL

[unemployment]

OK, I'll play with it over the next few days.

 

Please do, my dev team is trying to sort out this new system and quite honestly we just can't figure out an efficient way to do it.

[fenway]

Oh, I can definitely make it more efficient -- without a doubt.

[fenway]

" ON `vote_sent`.`uid` = `users`.`id`"

 

doesn't make any sense to me.  Why is there no user id in the vote table?

[unemployment]

" ON `vote_sent`.`uid` = `users`.`id`"

 

doesn't make any sense to me.  Why is there no user id in the vote table?

 

user id in the votes table is only added if the user is logged in.  If the user is not logged in then it is a public vote and no user id is recorded.  Does that make sense?

[fenway]

Then if would be vote.id, not vote.uid, right?

[unemployment]

Then if would be vote.id, not vote.uid, right?

 

I'm not sure what you mean?  However I originally wrote the sql I had it working and I believe in some cases I reference vote.uid to get all the votes from that user.

[fenway]

That doesn't make any sense.  Vote UID has nothing to do with the USER uid.

[fenway]

This is what I have so far... but I'm confused about ref_id vs user_id in the beta_keys table.

 

	SELECT
				CHAR_LENGTH(`users`.`credentials`) AS `cred_chars`,
				CHAR_LENGTH(`users`.`specialties`) AS `spec_chars`,
				`companies`.`companyvideo` AS `video`,
				`investor_info`.`accredited` AS `accredited`,
          ( SELECT COUNT(`user_id`) FROM `blog_posts` WHERE `user_id` = '1' ) AS `post_count`,
          ( SELECT COUNT(`uid`) FROM `votes` WHERE `uid` = '1' ) AS `vote_sent_count`,
				`votes_received`.`inv_vote_received_count`,
				`votes_received`.`pub_vote_received_count`,
          ( SELECT COUNT(`userid`) FROM `employees` WHERE `userid` = '1' ) AS `joined_count`,
				`votes2`.`ent_count`,
				`votes2`.`inv_count`,
			 (
					SELECT
						COUNT(`company_id`)
					FROM `company_fans`
					LEFT JOIN `companies` ON `company_fans`.`company_id` = `companies`.`companyid`
					LEFT JOIN `employees` ON `employees`.`companyid` = `companies`.`companyid`
					WHERE `userid` = '1'
			) AS
				`company_fans_count`,
          ( SELECT COUNT(`user_id`) FROM `recommendations` WHERE `user_id` = '1' ) AS `recommendation_count`,
          ( SELECT COUNT(`partner_id`) FROM `partners` WHERE (`user_id` = '1' OR `friend_id` = '1') AND `approved` = '1' ) AS `associate_count`
			 FROM `users`
         LEFT JOIN `investor_info` ON `investor_info`.`uid` = `users`.`id`
			 LEFT JOIN `employees` ON `employees`.`userid` = `users`.`id`
			 LEFT JOIN `companies` ON `employees`.`companyid` = `companies`.`companyid`
			 LEFT JOIN (
					SELECT
						`employees`.`userid`,
              				SUM( IF( `votes`.`vote_type` = 1, 1, 0 ) ) AS `inv_vote_received_count`,
              				SUM( IF( `votes`.`vote_type` = 0, 1, 0 ) ) AS `pub_vote_received_count`
					FROM `votes`
					INNER JOIN `employees` ON `employees`.`companyid` = `votes`.`company_id`
					WHERE `employees`.`userid` = '1'
            				GROUP BY `employees`.`userid`
        					) AS `votes_received` ON `votes_received`.`userid` = `users`.`id`

			 LEFT JOIN (
					SELECT
						`beta_keys`.`ref_id`,
              				SUM( IF( `users`.`accounttype` = 1, 1, 0 ) ) AS `ent_count`,
              				SUM( IF( `users`.`accounttype` = 0, 1, 0 ) ) AS `inv_count`
					FROM `beta_keys`
					INNER JOIN `users` ON `users`.`id` = `beta_keys`.`userid`
					WHERE `beta_keys`.`ref_id` = '1' AND `users`.`status` = 3
            				GROUP BY `users`.`id`
        					) AS `votes2` ON `votes2`.`ref_id` = `users`.`id`


			 WHERE `users`.`id` = '1'
;

[unemployment]

ref_id is the id of the person that sent the invite. Uid is the user id received for when completing sign up

[fenway]

Ah... then my GROUP BY in the beta_keys table should read "GROUP BY `users`.`id`" instead.

[unemployment]

Is that it though.  Did you finish working on it?

[fenway]

That's all I can do without redesigning everything.

[unemployment]

Very impressive fenway.  I just added in your sql.  You actually made the sql work correctly as what I had before wasn't even fully working though I didn't realize this until I saw your query output.  Thanks a lot.

[fenway]

Glad to help.