query fail

[unemployment]

For some reason my order by doesn't work.  Do you know why?

 

$sql  = "(
		SELECT
			`users`.`firstname`,
			`users`.`lastname`,
			`users`.`username`,
			`users`.`accounttype`,
			`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
		ORDER BY `users`.`lastname`
	)
	UNION ALL
	(
		SELECT
			`users`.`firstname`,
			`users`.`lastname`,
			`users`.`username`,
			`users`.`accounttype`,
			`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
		ORDER BY `users`.`lastname`
	)";

[vicodin]

You need to post the exact MySQL error your getting.

[blacknight]

when using joins you have to define every table i think so..

FROM `partners`

INNER JOIN `users`

would need something like

FROM `partners` AS partners

INNER JOIN `users` AS users

[kickstart]

Hi

 

You are doing the ordering prior to the UNION, rather than on the results of the UNION.

 

All the best

 

Keith