How to check another MYSQL join based on a previous join?

Question

I have the following scheme:

CREATE TABLE IF NOT EXISTS `connections` (
`username1` varchar(15) NOT NULL,
`username2` varchar(15) NOT NULL,
PRIMARY KEY (`username1`,`username2`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `connections` (`username1`, `username2`) VALUES
('guy', 'maricela'),
('maricela', 'guy'),
('mikha', 'guy');

CREATE TABLE IF NOT EXISTS `users` (
`username` varchar(15) NOT NULL,
`first_name` varchar(50) NOT NULL,
`last_name` varchar(50) NOT NULL,  
PRIMARY KEY (`username`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `users` (`username`, `first_name`, `last_name`) VALUES
('maricela', 'Maricela', 'Ros'),
('mikha', 'Michael', 'Samuel'),
('guy', 'Guy', 'Marquez');

The scheme contains 2 tables; one for user info called users with info first name,last name, and username. The 2nd table is connections. The column connections.username1 is the one who follows and connections.username2 is the one who is followed. I want to output a list of the people who follow the user maricela and for each result check if the resulting user is followed by the user mikha or not.

I use the following query but checking if the result is followed by mikha isn't working:

SELECT followed_by_mikha.username AS followed_by_mikha,
   users.first_name,
   users.last_name,       
   users.username      
   FROM connections
   LEFT JOIN users ON users.username = connections.username1
   LEFT JOIN users followed_by_mikha ON (connections.username1 = 'mikha' AND connections.username2 = users.username AND connections.username2 = followed_by_mikha.username)
   WHERE connections.username2 = 'maricela'
   ORDER BY users.username DESC LIMIT 10

Thanks :)

jcjr · Accepted Answer · 2012-12-23T16:26:32.507

1

SELECT fbm.username2 AS followed_by_mikha,
   users.first_name,
   users.last_name,       
   users.username      
   FROM connections
   LEFT JOIN users ON users.username = connections.username1

   LEFT JOIN connections fbm ON fbm.username2=connections.username1 and fbm.username1 = 'mikha'

   WHERE connections.username2 = 'maricela'
   ORDER BY users.username DESC LIMIT 10

edited Dec 23 '12 at 16:26

answered Dec 23 '12 at 16:21

jcjr

1,503
24
40

Is there another way to do it without subqueries? I believe subqueries perform slowly on a large scale..thanks – Michael Samuel Dec 23 '12 at 16:23
try the new one; seems working (doesn't check duplicates in connection table) – jcjr Dec 23 '12 at 16:27
I think it's working well :) but what do you mean by it doesn't check for duplicates? – Michael Samuel Dec 23 '12 at 16:33
if you have two lines ('mikha', 'guy') in connection, you will have two lines in result, although ('guy', 'maricela') is just once; anyway, you can restrict that by defining unique key on two columns in connection table – jcjr Dec 23 '12 at 16:35
I already have a unique key on `username1` and `username2` so `mikha,guy` can't exist twice but `mikha,guy` and `guy,mikha` can both exist..that wouldn't produce duplicates...right?? have another question, do joins perform better than subqueries?? thanks :) – Michael Samuel Dec 23 '12 at 16:40

How to check another MYSQL join based on a previous join?

1 Answers1