开发者

Mysql query generation

This is my user table schema

CREATE TABLE IF NOT EXISTS `ehobe_user` (
  `user_id` bigint(20) NOT NULL,
  `user_email` varchar(80) NOT NULL,
  `user_password` varchar(50) NOT NULL,
  `user_fname` varchar(255) NOT NULL,
  `user_lname` varchar(255) N开发者_如何学GoOT NULL,
  `user_terms` enum('yes','no') NOT NULL DEFAULT 'yes',
  `is_active` enum('yes','no') NOT NULL DEFAULT 'yes',
  `created_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

And this is my friends table schema

CREATE TABLE IF NOT EXISTS `ehobe_friends` (
  `user_id1` bigint(20) NOT NULL,
  `user_id2` bigint(20) NOT NULL,
  `relationship_id` int(1) NOT NULL COMMENT '1 - user1 request, 2- user2 request, 3 - friends, 4- user1 blocked, 5 - user2 blocked'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

So i need to select the user first name and last name who are my frinds in the friends table.


Suppose you want friends of USER_ID #67.

Try:

select u.user_id, u.user_fname, u.user_lname
  from ehobe_user u
 inner join ehobe_friends f1 on (u.user_id = f1.user_id1)
 where f1.user_id2 = 67
 union
select u.user_id, u.user_fname, u.user_lname
  from ehobe_user u
 inner join ehobe_friends f2 on (u.user_id = f2.user_id2)
 where f2.user_id1 = 67


Logically, you want to get all of the friends in an array and check each user for matching info. I would give you code, but I don't know what language you're writing this in.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜