Counts from 2 alias'of one table.

[ToonMariner]

Hi peeps,

 

This one is baffling me a bit.

 

I have a table of journeys where a user is either the sender or the recipient.  I want to select the count of number of times user has sent and number of times user has been recipient and the respective distances...

 

I have the following query

<?php
$qry	=	"
		SELECT
			`textfull`.`text` AS `bio` ,
			`l_cities`.`latitude` AS `lat` ,
			`l_cities`.`longitude` AS `long` ,
			`users`.`username` AS `username` ,
			DATE_FORMAT(`user_profile`.`date` , '%D of %M %Y') AS `datejoined` ,
			`l_countries`.`long_name` AS `country` ,
			COUNT(`scard_journey`.`journey_id`) AS `sentcards` ,
			SUM(`scard_journey`.`distance`) AS `distance` ,
			COUNT(`rcard_journey`.`journey_id`) AS `receivedcards` ,
			SUM(`rcard_journey`.`distance`) AS `rdistance` ,
			IF	(
				COUNT(`activity`.`last_action`) > 0 ,
				`activity`.`last_action` ,
				'no record'
				)
			AS `laston`
		FROM
			`user_profile`
		LEFT JOIN
			`users`
		ON
			`users`.`user_id` = `user_profile`.`user_id`
		LEFT JOIN
			`user_location`
		ON
			`user_location`.`user_id` = `user_profile`.`user_id`
		LEFT JOIN
			`l_cities`
		ON
			`l_cities`.`city_id` = `user_location`.`city_id`
		LEFT JOIN
			`l_regions`
		ON
			`l_regions`.`region_id`	=	`l_cities`.`region_id`
		LEFT JOIN
			`l_countries`
		ON
			`l_countries`.`iso`	=	`l_regions`.`country_iso2`
		LEFT JOIN
			`textfull`
		ON
			`textfull`.`text_id` = `user_profile`.`bio`
		LEFT JOIN
			`card_journey` AS `rcard_journey`
		ON
			`rcard_journey`.`recipient` = `user_profile`.`user_id`
		LEFT JOIN
			`card_journey` AS `scard_journey`
		ON
			`scard_journey`.`sender` = `user_profile`.`user_id`
		LEFT JOIN
			`activity`
		ON
			`activity`.`user_id`	=	`user_profile`.`user_id`
		WHERE
			`users`.`username`	=	:username
		GROUP BY
			`scard_journey`.`sender` ,
			`rcard_journey`.`recipient`
		";
$getCards	=	$this->prepare ( $qry );
$getCards->bindValue ( ':username' , $user , PDO::PARAM_STR );
$getCards->execute ();
$arr	=	$getCards->fetchAll ( PDO::FETCH_ASSOC );
return $arr;
?>

 

unfortunately the values retruned for count recipient, count sender AND the distance sent and received are sums of all instances where the user id occurs in the 'card_journeys' table.

 

IE if user 'xyz' has sent 4 and received 2 with respective distances of 1000 miles and 300 only 4 and 1000 are being returned.

 

Hope you can read into what I'm getting at and I would really appreciate any tips.

 

Thanks in advance.

[fenway]

I don't follow.