user3487243 user3487243 - 5 months ago 13
MySQL Question

Remove distinct row if NULL column exists from SELECT

I have a table of transactions,

History[CustomerID, BikeID,Checkout,StationIDout,Checkin,StationIDin]


where
Checkout
and
Checkin
are
GETDATE()


I want to query
History
so that I receive bikes that are currently at a given StationID, (i.e.
Checkin
and
StationIDin
are
NOT NULL
)


I am currently using this query:

SELECT DISTINCT BikeID from History
where StationIDin = {0}
AND Checkin IS NOT NULL AND StationIDin IS NOT NULL


Not surprisingly, I am still receiving bikes that are checked out, because my query picks out the first
BikeID
it sees with a completed transaction without searching for another instance where the same bike is NOT checked back in.

I would like to form a query that does NOT select
BikeID
that are checked out. In other words, to search for any occurrence of NULL in StationIDin and eliminate it from the result before selecting the
BikeID
.

(My apologies for potentially misnaming the title, I can't describe it better)

CustomerID BikeID Checkout StationIDout Checkin StationIDin
--------------------------------------------------------------------
1001 32 6/8/16 500 6/28/16 500
1002 21 1/2/15 500 1/3/15 500
1003 32 3/7/15 500 NULL NULL

CURRENT OUTPUT:
32
21

DESIRED OUTPUT:
21

Answer

Use GROUP BY, not SELECT DISTINCT:

SELECT BikeID 
FROM History h
WHERE StationIDin = {0} 
GROUP BY BikeId
HAVING SUM(CheckIn IS NULL) = 0;

This returns all bikes that have no record with CheckIn as NULL.

Comments