Mysql query generation

Question

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.

Pablo Santa Cruz · Accepted Answer · 2010-11-08 20:09:13Z

3

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

answered Nov 8, 2010 at 20:09

Pablo Santa Cruz

182k33 gold badges250 silver badges300 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tmas · Accepted Answer · 2010-11-08 20:08:50Z

0

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.

answered Nov 8, 2010 at 20:08

Tmas

551 silver badge8 bronze badges

2 Comments

pravat231 Over a year ago

i need only mysql query to fetch all the details of my friends who are present in the friends table with relatiionship_id=3

Tmas Over a year ago

In that case, here you go: SELECT * FROM ehobe_friends WHERE relationship_id=3. If you want the separate fields, replace * with the name of the field you want.

Collectives™ on Stack Overflow

Mysql query generation

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related