Join Question

[unemployment]

Can you left join after you use a union all?

[kickstart]

Hi

 

Depends what you mean.

 

UNION / UNION ALL joins the results of 2 (or more) queries together and those queries could contain JOINS.

 

Outside of that you could use a LEFT OUTER JOIN to join the results of the UNION (ie, as a subselect) with another table / subselect.

 

All the best

 

Keith

[unemployment]

Keith can you please help me with this...

 

I tried doing what you said but I get #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 'LEFT OUTER JOIN `company_fans` ON `partners`.`user_id` = `company_fans`.`use' at line 74

 

(
			SELECT
				`users`.`firstname`,
				`users`.`lastname`,
				`users`.`username`,
				`users`.`accounttype`,
				`users`.`country`,
				`users`.`state`,
				`users`.`city`,
				`partners`.`approved_date`,
				`current`.`total` AS `total_a`,
				`friend`.`total` AS `total_b`
			FROM `partners`
			INNER JOIN `users`
			ON `partners`.`user_id` = `users`.`id`
			LEFT JOIN (
				SELECT
					`user_id` AS `uid`,
					COUNT(`partner_id`) AS `total`
				FROM `partners`
				WHERE `approved` = 1
				GROUP BY `user_id`
			) AS `current`
			ON `partners`.`user_id` = `current`.`uid`
			LEFT JOIN (
				SELECT
					`friend_id` AS `uid`,
					COUNT(`partner_id`) AS `total`
				FROM `partners`
				WHERE `approved` = 1
				GROUP BY `friend_id`
			) AS `friend`
			ON `partners`.`user_id` = `friend`.`uid`
			WHERE `partners`.`friend_id` = ${uid}
			AND `approved` = 1
		)
		UNION ALL
		(
			SELECT
				`users`.`firstname`,
				`users`.`lastname`,
				`users`.`username`,
				`users`.`accounttype`,
				`users`.`country`,
				`users`.`state`,
				`users`.`city`,
				`partners`.`approved_date`,
				`current`.`total` AS `total_a`,
				`friend`.`total` AS `total_b`
			FROM `partners`
			INNER JOIN `users`
			ON `partners`.`friend_id` = `users`.`id`
			LEFT JOIN (
				SELECT
					`user_id` AS `uid`,
					COUNT(`partner_id`) AS `total`
				FROM `partners`
				WHERE `approved` = 1
				GROUP BY `user_id`
			) AS `current`
			ON `partners`.`friend_id` = `current`.`uid`
			LEFT JOIN (
				SELECT
					`friend_id` AS `uid`,
					COUNT(`partner_id`) AS `total`
				FROM `partners`
				WHERE `approved` = 1
				GROUP BY `friend_id`
			) AS `friend`
			ON `partners`.`friend_id` = `friend`.`uid`
			WHERE `partners`.`user_id` = ${uid}
			AND `approved` = 1
		)
		LEFT OUTER JOIN `company_fans`
		ON `partners`.`user_id` = `company_fans`.`user_id`
		ORDER BY `approved_date` DESC
		LIMIT 3

 

UPDATE:  p.s.  Everything else besides the LEFT OUTER JOIN works.

[kickstart]

Hi

 

Use the 2 UNIONed queries in the FROM clause of a SELECT, JOINed with the other table you want.

 

I have no idea what you want from the 2nd table, but something like this would do it.

 

SELECT a.firstname, a.lastname, a.username, a.accounttype, a.country, a.users, a.`state`, a.users, a.city, a.approved_date, a.total_a, a.total_b, b.somefield
FROM ((
SELECT
	`users`.`firstname`,
	`users`.`lastname`,
	`users`.`username`,
	`users`.`accounttype`,
	`users`.`country`,
	`users`.`state`,
	`users`.`city`,
	`partners`.`approved_date`,
	`current`.`total` AS `total_a`,
	`friend`.`total` AS `total_b`
FROM `partners`
INNER JOIN `users`
ON `partners`.`user_id` = `users`.`id`
LEFT JOIN (
	SELECT
		`user_id` AS `uid`,
		COUNT(`partner_id`) AS `total`
	FROM `partners`
	WHERE `approved` = 1
	GROUP BY `user_id`
) AS `current`
ON `partners`.`user_id` = `current`.`uid`
LEFT JOIN (
	SELECT
		`friend_id` AS `uid`,
		COUNT(`partner_id`) AS `total`
	FROM `partners`
	WHERE `approved` = 1
	GROUP BY `friend_id`
) AS `friend`
ON `partners`.`user_id` = `friend`.`uid`
WHERE `partners`.`friend_id` = ${uid}
AND `approved` = 1
)
UNION ALL
(
SELECT
	`users`.`firstname`,
	`users`.`lastname`,
	`users`.`username`,
	`users`.`accounttype`,
	`users`.`country`,
	`users`.`state`,
	`users`.`city`,
	`partners`.`approved_date`,
	`current`.`total` AS `total_a`,
	`friend`.`total` AS `total_b`
FROM `partners`
INNER JOIN `users`
ON `partners`.`friend_id` = `users`.`id`
LEFT JOIN (
	SELECT
		`user_id` AS `uid`,
		COUNT(`partner_id`) AS `total`
	FROM `partners`
	WHERE `approved` = 1
	GROUP BY `user_id`
) AS `current`
ON `partners`.`friend_id` = `current`.`uid`
LEFT JOIN (
	SELECT
		`friend_id` AS `uid`,
		COUNT(`partner_id`) AS `total`
	FROM `partners`
	WHERE `approved` = 1
	GROUP BY `friend_id`
) AS `friend`
ON `partners`.`friend_id` = `friend`.`uid`
WHERE `partners`.`user_id` = ${uid}
AND `approved` = 1
)) a
LEFT OUTER JOIN company_fans b
ON a.user_id = b.user_id
ORDER BY a.approved_date DESC
LIMIT 3

 

All the best

 

Keith