CODEWITHSUNDEEP
sqlite
imk8
imk8

Reputation: 3

Compare 2 columns and find unique values only

apologies in advance if my formatting is bad, new here.

I'm trying to obtain unique values only, from 2 separate columns that are in the same table. For example : https://i.sstatic.net/SKZjT.png

When comparing column1 with column2 I want to obtain the result g

When comparing column2 with column1 I want to obtain the result h

I'm trying to implement this logic but it doesn't work, any advise would be appreciated!

SELECT column1 FROM list WHERE column1!=column2

Edit 1:

Here is the actual data sets I'm working with: https://i.sstatic.net/VfOU5.png

Basically I'm writing a small program to check if the accts that I follow are also followers ( and then vice versa, but that's for later)

Here is the code(as suggested below) that I'm trying but only prints out []:

# Open datas.db file for SQLite
db = cs50.SQL("sqlite:///datas.db")

followeronly = db.execute("SELECT followers FROM lists WHERE followers NOT IN(SELECT following FROM lists)")
print(followeronly)

Sorry for the confusion!

Upvotes: 0

Views: 1473

Answers (1)

allenfromspace
allenfromspace

Reputation: 140

The WHERE clause in your query only evaluates the columns within a single row at a time. So for the first row, the WHERE clause compares a != h, which is true, and the row is added to the result set. This happens for all of the other rows too, because column1 never equals column2 in any of your rows. So all rows are returned. You need to check whether column1 exists in any row's column2 each time you're evaluating a row's column1.

One possible way to get what you want is to use an IN comparison with a nested query in the WHERE clause:

SELECT column1 FROM list WHERE column1 NOT IN (SELECT column2 FROM list)

Upvotes: 2

Related Questions