SQL select no result

Question

The SQL below returns no result but there is one record in the table:

SELECT DISTINCT ul.* FROM UserLogin ul, ContactPerson cp, UserRole url, Role rl

If I run this query, it returns the record:

SELECT DISTINCT ul.* FROM UserLogin ul

Can you help explain this and how to make the first one work?

Answer 1

You need to join all the tables ul, cp, url, rl. For example

SELECT distinct ul.* FROM UserLogin ul join ContactPerson cp
on ul.id=cp.user

Answer 2

Because you're pulling from 4 different tables:

UserLogin ContactPerson UserRole Role

You need to do either a UNION all or a JOIN, otherwise most of the time it will return blank.

Answer 3

This:

SELECT distinct ul.* 
  FROM UserLogin ul

...works because it is selecting from a single table, while this:

SELECT distinct ul.* 
  FROM UserLogin ul,
       ContactPerson cp,
       UserRole url,
       Role rl

...is using non ANSI JOIN syntax to join the USERLOGIN, CONTACTPERSON, USERROLE and ROLE tables together. In order for records to be returned from the query, there has to be records that exist in all tables involved. To visualize it, it would look like this:

The blue portion represents the records that would be returned from a query similar to yours.

To get records from all those tables, we have to know how they relate to one another.

Here is an example of your query using ANSI join syntax, including assumptions on how the tables relate in order to get the query to return results:

SELECT DISTINCT ul.*
  FROM CONTACTPERSON cp
  JOIN USERLOGIN ul ON ul.user_id = cp.user_id
  JOIN USERROLE ur ON ur.user_id = ul.user_id
  JOIN ROLE r ON r.role_id = ur.role_id

It's not clear what the relationship between USERLOGIN and CONTACTPERSON are...

I highly recommend reading this article on SQL JOINs.

ANSI vs Non ANSI JOIN syntax

ANSI JOIN syntax is recommended - it's more readable, separates the actual WHERE clause criteria from the JOIN criteria, and is supported across various databases making queries more likely to be portable.

Answer 4

Your SELECT statement lists 4 tables and does a cartesian product of them.

Since the SELECT returns no rows, one of the tables must be empty.

Since you assert that table UserLogin contains one row, at least one of the other three tables must be empty.