Search code examples
sqlsql-serversubqueryinner-joinnot-exists

Select Data not have a key in other table

I have two tables 1- Dates Table

------------
|    Date   |
------------
| 1/11/2020 |
-----------
| 2/11/2020 |
------------

2- Revenues

    ------------------------------------
    |  revDate  |  Name     |  Revenue  |
    ------------------------------------
    | 1/11/2020 |  Joe      |   500 $   |
    ------------------------------------
    | 2/11/2020 |   Dani    |   400 $   |
    ------------------------------------
    | 4/11/2020 |   Sami    |   300 $   |
    ------------------------------------

I need a query to return name of user not submit a revenue in a date table the query should return :

    ------------------------------------
    |  Date     |  UserNotSubmit        |
    ------------------------------------
    | 1/11/2020 |  Dani                 |
    ------------------------------------
    | 1/11/2020 |  Sami                 |
    ------------------------------------
    | 2/11/2020 |  Joe                  |
    ------------------------------------
    | 2/11/2020 |  Sami                 |
    ------------------------------------
Solution

  • You can generate all combinations of dates and names with a cross join then fiter out those that exist:

    select d.date, n.name
from dates
cross join (select distinct name from revenues) n
where not exists (select 1 from revenues r where r.revdate = d.date and r.name = n.name)