Select columns from table where value of columns not equal 'myvalue'

Question

I have a very critical code. I'm trying to select all records from two columns of one table that matches the records of another table in one column. here's a my result so far

SELECT username FROM users 
WHERE username NOT IN (SELECT friend FROM friends WHERE friend = 'user1') 
AND username NOT IN (SELECT you FROM friends WHERE you = 'user1')

live demo

This can't be right... I want the records in column [username] that doesn't match 'user1' in both columns [you] and [friend] in table {friends} to be a result of that sql selection. please see the demo for more understanding. thanks for your help.

Problem solved, hopefully this can help other people

SELECT username,you,friend FROM users, friends 
WHERE you IN (SELECT you FROM friends WHERE you != 'user1') 
AND friend IN (SELECT friend FROM friends WHERE friend != 'user1')
AND username IN (you,friend)

and this is a Live demo

Why can't you use a regular join and <> to check the fields aren't equal to both values? Or what rows do you want returned in your fiddle? — Jeanne Boyarsky, Oct 19 '13 at 19:06
Because I'm trying to elicit what you WANT to happen. With data. Which rows of the three in the sample do you want the query to return? — Jeanne Boyarsky, Oct 19 '13 at 19:10
all the usernames from table users that doesn't mactch 'user1' in table friends in columns 'you' and 'friend' — user2898514, Oct 19 '13 at 19:13
yes, every 'you' who's not a friend with 'user1' and every 'friend' who's 'you' is not 'user1' that match the usernames in table users — user2898514, Oct 19 '13 at 19:18

score 0 · Accepted Answer · edited May 23 '17 at 12:21

0

We are now down to two ways what you want can be interpreted.

Option1 - this is exactly what I think you asked for, but not what I think you want.

SELECT username FROM users u, friends f
WHERE username = f.friend and f.friend <> 'user1'
union
SELECT username FROM users u, friends f
WHERE username = f.you and f.you <> 'user1'

Option2 - this is what I think you want. See how to translate it to mysql. SELECT username FROM users u, friends f WHERE username = f.friend and f.friend <> 'user1' intersect SELECT username FROM users u, friends f WHERE username = f.you and f.you <> 'user1'

edited May 23 '17 at 12:21

Community

1
1

answered Oct 19 '13 at 19:22

Jeanne Boyarsky

12,156
2
49
59

The second option doesn't work the first option works pretty much like mine I wanted both columns records of table friends that doesn't equal 'user1' to return and match usernames in tables users and then return those usernames – user2898514 Oct 19 '13 at 19:31
http://sqlfiddle.com/#!2/2f134/53 these results might give u a picture of what I want. – user2898514 Oct 19 '13 at 20:25
SELECT you,friend FROM friends WHERE you <> 'user1' and friend <> 'user1' returns that and is very simple. – Jeanne Boyarsky Oct 19 '13 at 21:28

Select columns from table where value of columns not equal 'myvalue'

1 Answers1