I have users
table:
CREATE TABLE `users` (
`uid` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`email` varchar(70) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`flname` varchar(60),
PRIMARY KEY (`uid`)
) ENGINE=InnoDB
And a relationship table:
CREATE TABLE `relationship` (
`rid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`from` mediumint(8) unsigned NOT NULL,
`to` mediumint(8) unsigned NOT NULL,
PRIMARY KEY (`rid`),
UNIQUE KEY `from_2` (`from`,`to`),
KEY `to` (`to`),
CONSTRAINT `relationship_ibfk_1` FOREIGN KEY (`from`) REFERENCES `users` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `relationship_ibfk_2` FOREIGN KEY (`to`) REFERENCES `users` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
I want to fetch those users that is not in a specific user's circle (Like G+). It means that user has not yet added those people to his/her circle. I tried the below query but it was empty:
SELECT uid FROM users WHERE flname LIKE'%john%' AND NOT EXISTS (Select `to` FROM relationship WHERE `from`=60)
The below query returns 3 records:
SELECT uid,flname FROM users WHERE flname LIKE'%john%'
Now users in the name of john
who has been added by uid=60 should not have be shown! The result of this query is empty. I can't figure that out, what I'm doing wrong?
Best Answer
The subquery should be correlated:
(unrelated to the issue)
Whoever told you that it's a good idea to use reserved words like
to
andfrom
as column or table names, deserves a kick.You can have the same results with a
LEFT JOIN / IS NULL
or aNOT IN
query: