CODEWITHSUNDEEP
mysql
pravat231
pravat231

Reputation: 780

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) NOT 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.

Upvotes: 2

Views: 59

Answers (2)

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181280

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

Upvotes: 3

Tmas
Tmas

Reputation: 55

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.

Upvotes: 0

Related Questions