CODEWITHSUNDEEP
sqlsql-serverouter-join
Ivan Pudic
Ivan Pudic

Reputation: 71

SQL Server: query values and show null if no result

I have 3 tables

I'm trying to get a query to return me values like this:

------------------------------------------------------------
| Date          | Username         | Badge timestamp       |
------------------------------------------------------------
| 01-jan-2018   | Steve            | null                  |
| 01-jan-2018   | Peter            | 01-01-2018T08:25:21   |
| 01-jan-2018   | Sasha            | null                  |
| 02-jan-2018   | Steve            | null                  |
| 02-jan-2018   | Peter            | null                  |
| 02-jan-2018   | Sasha            | null                  |
| 03-jan-2018   | Steve            | 03-01-2018T12:01:20   |
| 03-jan-2018   | Peter            | 03-01-2018T06:39:01   |
| 03-jan-2018   | Sasha            | 03-01-2018T09:11:15   |
------------------------------------------------------------

The query should go and show results for each value in table Kalender and for each person in table GeoPers and then show me their badge timestamp and if not then NULL

I have gotten so far:

select 
    k.datum, isnull(gp.name,null), isnull(gt.DateTimeLocal,null)
from 
    Kalender k
left outer join 
    GeoTikkingen gt on CAST(k.datum as DATE) = CAST(gt.DateTimeLocal as DATE)
left outer join 
    GeoPers gp on gp.name = gt.UserName
order by 
    k.datum asc, gp.name asc

But when a user has not badged on a specific day, it doesn't return NULL

I should be able have a list of all values in Kalender AND all values in GeoPers, have they badged then timestamp if not NULL

Please help :-)

Upvotes: 0

Views: 149

Answers (3)

Zack
Zack

Reputation: 580

Try this:

SELECT k.datum,
       gp.name,
       gt.DateTimeLocal
  FROM Kalender k,
       GeoPers gp
       LEFT OUTER JOIN GeoTikkingen gt
                    ON Cast(k.datum AS DATE) = Cast(gt.DateTimeLocal AS DATE)
                       AND gp.name = gt.UserName
 ORDER BY k.datum ASC,
          gp.name ASC

Upvotes: 0

Dan Bracuk
Dan Bracuk

Reputation: 20804

I think cross join is the way to go. Here is something I did to confirm my opinion. You can adopt it to your database objects:

with a as (select 1 record
union select 2 record)
, b as (select 'a' text
union select 'b' text)
,c as (select 1 r
union select 4 r)

select record, text, r
from a cross join b
left join c on record = r

Upvotes: 0

Emre Kabaoglu
Emre Kabaoglu

Reputation: 13146

I think, you should apply full outer join;

select k.datum, gp.name, gt.DateTimeLocal
from Kalender k
full outer join GeoTikkingen gt on CAST(k.datum as DATE) = CAST(gt.DateTimeLocal as DATE)
full outer join GeoPers gp on gp.name = gt.UserName
order by k.datum asc, gp.name asc

And remove isnull(gt.DateTimeLocal,null) statement, it is completely in wrong usage.

Upvotes: 1

Related Questions