I am trying to create a friend relationship query. I want the following query to do two things, rather than making another very similar query.
I am wanting to check for pending requests and sent requests. Pending requests are when the user is friend_two. Sent requests are when friend_one is the user_id.
So, is there anyway I can adjust my where clause to something like this (I do not know how to write this out in SQL) -
WHERE friend_one = ? As pending_user
WHERE friend_two = ? AS sent_user
SELECT *, COUNT(friend_one) AS pending_count , COUNT(friend_two) AS requests_sent
WHERE friend_one OR friend_two = ?
AND status = ?
CREATE TABLE `friends` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`friend_one` int(11) NOT NULL DEFAULT '0',
`friend_two` int(11) NOT NULL DEFAULT '0',
`status` enum('0','1','2') COLLATE utf8_unicode_ci DEFAULT '0',
`date` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `friend_two` (`friend_two`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
@name, @status parameters. I belive you want something like this.
SELECT COUNT(CASE WHEN `friend_one` = @name THEN 1 END) as `pending_count`, COUNT(CASE WHEN `friend_two` = @name THEN 1 END) as `requests_count` FROM friends WHERE @name IN ( `friend_one` , `friend_two` ) AND `status` = @status