CODEWITHSUNDEEP
sqlpostgresql
Beauregard Lionett
Beauregard Lionett

Reputation: 321

Query returns 0 rows when it should return n results

I'm learning SQL using PostgresSQL.

I have a very simple query that in my understanding should return 3 results.

Here is the table (simplified because the one I parsed was all screwed up):

  fname   |  lname  |    ssn    | super_ssn 
----------+---------+-----------+-----------
 James    | Borg    | 888665555 | 
 John     | Smith   | 123456789 | 333445555
 Franklin | Wong    | 333445555 | 888665555
 Alicia   | Zelaya  | 999887777 | 987654321
 Jennifer | Wallace | 987654321 | 888665555
 Ramesh   | Narayan | 666884444 | 333445555
 Joyce    | English | 453453453 | 333445555
 Ahmad    | Jabbar  | 987987987 | 987654321

So, by looking into this, I know that there are three names there that should be managers, since they are present in the super_ssn column.

When I do:

SELECT fname, lname
FROM employee
WHERE super_ssn = ssn;

I get 

 fname | lname 
-------+-------
(0 rows)

Why is that?

The column ssn, and the column super_ssn are both character types.

As I said, I'm learning it, so be slightly more in depth with your answer if you can. Please and thank you.

Upvotes: 0

Views: 37

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

There are no rows where:

WHERE super_ssn = ssn;

The WHERE clause only considers values in the same row. Hence, your query returns no rows.

I suspect you want IN or EXISTS:

SELECT e.fname, e.lname
FROM employee e
WHERE EXISTS (SELECT 1
              FROM employee e2
              WHERE e2.super_ssn = e.ssn
             );

Upvotes: 2

Related Questions