CODEWITHSUNDEEP
mysqldatabasenull
aanders77
aanders77

Reputation: 630

Get rid of null values in mysql query

I have a database where I store fishing licenses. The licenses have from- and to-dates, and I'm using a calendar table to count the amount of sold licenses for each day. Each license has a defined licensetype. The licensetype can be valid for one or more fishing zones.

I'm trying to build a query which shows some columns from the zones table and how many licenses have been sold grouped by day and fishing zone, and filtered by licensetype. I've made it work for the days where licenses have been sold. However, the days where no licenses have been sold yet show only null values.

I've been twisting my head around this for 8 hours now. I'm sure there's an easy solution, I just can't see it. SQL Fiddle here.

I think the schema is quite self-explanatory, so I won't include it here. Please see the fiddle if needed.

SET @licensetype = 1,
    @fromdate = '2019-01-01',
    @todate = '2019-01-10';

SELECT zoneID,
   dy,
   seasonmax,
   daymax,
   COUNT(lID) AS sold
FROM
( SELECT z.zoneID,
       z.seasonmax,
       z.daymax,
       l.ID AS lID,
       l.From,
       l.To,
       lt.ValidForZone
FROM zones z
LEFT JOIN licensetypes lt ON z.zoneID IN(lt.ValidForZone)
LEFT JOIN licenses l ON lt.ID = l.TypeID
WHERE FIND_IN_SET( z.zoneID,
                   ( SELECT lt2.ValidForZone
                    FROM licensetypes lt2
                    WHERE lt2.ID = @licensetype ) ) ) derived
RIGHT JOIN calendar ON calendar.dy >= DATE_FORMAT(derived.From, '%Y-%m-%d')
AND calendar.dy < DATE_FORMAT(derived.To, '%Y-%m-%d')
WHERE calendar.dy >= @fromdate
AND calendar.dy <= @todate
GROUP BY dy,
     zoneID
ORDER BY dy

Upvotes: 1

Views: 99

Answers (3)

Nick
Nick

Reputation: 147166

I think this query should give you the desired results (which seems to be 1 for 3/1, 2 for 4/1 and 5/1). You have to start from a CROSS JOIN of days and zones, then LEFT JOIN to licensetypes and licenses on the relevant license type and days:

SELECT z.zoneID, DATE(c.dy) AS dy, z.seasonmax, z.daymax, COUNT(l.ID) AS sold
FROM calendar c
CROSS JOIN zones z
LEFT JOIN licensetypes t ON t.ID = @licensetype AND FIND_IN_SET(z.zoneID, t.ValidForZone)
LEFT JOIN licenses l ON l.TypeID = t.ID AND DATE(c.dy) >= DATE(l.From) AND DATE(c.dy) < DATE(l.To)
WHERE DATE(c.dy) BETWEEN @fromdate AND @todate
GROUP BY dy, z.zoneID

Demo on SQLFiddle

Upvotes: 1

Barmar
Barmar

Reputation: 781004

If you want a row for every zone, even the ones that weren't sold on that day, you can take the zones table out of the subquery. You want to start with a CROSS JOIN between the zones and calendar table to get all combinations of zones and dates. Then LEFT JOIN that with the license information to get the license counts.

Also, to join with a comma-separated list you need to use FIND_IN_SET, not IN(). See Search with comma-separated value mysql.

SELECT z.zoneID, c.dy, z.seasonmax, z.daymax, IFNULL(COUNT(l.ID), 0) AS sold
FROM zones AS z
CROSS JOIN calendar AS c
JOIN licensetypes AS lt ON FIND_IN_SET(z.zoneID, lt.ValidForZone)
LEFT JOIN licenses AS l ON lt.ID = l.typeID AND c.dy >= DATE_FORMAT(l.From, '%Y-%m-%d') AND c.dy < DATE_FORMAT(l.To, '%Y-%m-%d')
WHERE c.dy BETWEEN @fromdate AND @todate
    AND lt.ID = @licensetype
GROUP BY z.zoneID, c.dy
ORDER BY dy

Upvotes: 2

GMB
GMB

Reputation: 222472

Thanks for the db fiddle. I have largely modified your query, as per following guidelines :

  • The subquery does not seem necessary : the computation can be achieved in a single, GROUP BY query
  • You need a cartesian product to generate the list of all dates for all zones, hence FROM calendar CROSS JOIN zones ; the general filters coming from the parameters, that apply to calendarand zones, and can be placed in the WHERE clause
  • Every filtering condition on LEFT JOINed tables should be moved to the ON clause of the relevant join
  • it is more efficient to use the DATE function than DATE_FORMAT to trunc a datetime to date

New query (see the db fiddle) :

SELECT z.zoneID, calendar.dy, z.seasonmax, z.daymax, COUNT(DISTINCT l.ID) Asold
FROM 
    calendar
    CROSS JOIN zones z
    LEFT JOIN licensetypes lt ON z.zoneID IN(lt.ValidForZone)
    LEFT JOIN licenses l 
        ON  lt.ID = l.TypeID 
        AND calendar.dy BETWEEN DATE(l.From) AND DATE(l.To)
WHERE
    FIND_IN_SET( z.zoneID, ( SELECT lt2.ValidForZone FROM licensetypes lt2 WHERE lt2.ID = @licensetype ) )
    AND calendar.dy >= @fromdate
    AND calendar.dy <= @todate
GROUP BY z.zoneID, calendar.dy, z.seasonmax, z.daymax
ORDER BY dy

NB : this query does not properly takes in account the case where multiple comma-separated values are stored in licensetypes.ValidForZone : when several licence types match, only the first one is counted (just like your original query). It would be better to normalize your database and use a bridge table to store the 1-N relation between license types and zones, instead of stuffing N values in a single field...

Upvotes: 1

Related Questions