Populating NULLS from LEFT JOIN with values

Question

SELECT

    rates_Calendar.date,
    subQuery.name,
    COALESCE(subQuery.amount,0) as amount,
    subQuery.reference,
    subQuery.property


FROM
    rates_Calendar
    LEFT JOIN (
                SELECT
                    rates_Booking.date,
                    unit.unit,
                    unit.abbreviation as name,
                    rates_Booking.amount,
                    rates_Booking.bookingReference AS reference,
                    property.property


                FROM
                    rates_Booking

                    LEFT JOIN booking ON booking.reference = rates_Booking.bookingReference

                    LEFT JOIN unit ON booking.apartment = unit.unit

                    LEFT JOIN property ON property.property = unit.property

                    # unit to apartments
                    LEFT JOIN apartments ON (apartments.unit = unit.unit)
                    LEFT JOIN apartmentTypes ON (apartmentTypes.id = apartments.apartmentTypeId)

                WHERE
                    rates_Booking.date BETWEEN @startDate AND @endDate
                    AND unit.unit = 221



                GROUP BY
                    property.area,
                    property.property,
                    apartmentTypes.id,
                    unit.unit,
                    rates_Booking.date

                ) AS subQuery ON subQuery.date = rates_Calendar.date


 WHERE
     rates_Calendar.date BETWEEN @startDate AND @endDate
 GROUP BY 
subQuery.reference,
subQuery.unit,
subQuery.apartmentTypeId,
subQuery.property,
subQuery.area,
    rates_Calendar.date

Now, obviously this query will result in NULLS for dates that do not match. Is there a way to update all the NULLS with the NON NULL values?

2013-01-01  unitA 138 1      property1
2013-01-02  unitA 138 1      property1
2013-01-03  unitA 138 1      property1
2013-01-04  NULL  0   NULL   NULL
2013-01-05  NULL  0   NULL   NULL

Is there a way to update the NULLs with the NON NULLS in the respective columns ?

I'm trying for this because hiding rowGroups with NULLS is not possible as can be understood from the link: Hide NULL Row Groups JasperReports

Itchy · Accepted Answer · 2013-01-09T16:22:19.130

1

I think you don't want a LEFT JOIN then with your subQuery. Try this:

SELECT

    rates_Calendar.date,
    subQuery.name,
    COALESCE(subQuery.amount,0) as amount,
    subQuery.reference,
    subQuery.property


FROM
    rates_Calendar, (
                SELECT
                    rates_Booking.date,
                    unit.unit,
                    unit.abbreviation as name,
                    rates_Booking.amount,
                    rates_Booking.bookingReference AS reference,
                    property.property


                FROM
                    rates_Booking

                    LEFT JOIN booking ON booking.reference = rates_Booking.bookingReference

                    LEFT JOIN unit ON booking.apartment = unit.unit

                    LEFT JOIN property ON property.property = unit.property

                    # unit to apartments
                    LEFT JOIN apartments ON (apartments.unit = unit.unit)
                    LEFT JOIN apartmentTypes ON (apartmentTypes.id = apartments.apartmentTypeId)

                WHERE
                    rates_Booking.date BETWEEN @startDate AND @endDate
                    AND unit.unit = 221



                GROUP BY
                    property.area,
                    property.property,
                    apartmentTypes.id,
                    unit.unit,
                    rates_Booking.date

                ) AS subQuery


 WHERE
   rates_Calendar.date BETWEEN @startDate AND @endDate

edited Jan 09 '13 at 16:22

answered Jan 09 '13 at 16:15

Itchy

2,263
28
41

IFNULL can probably be used but how can I get the NULLS to be updated with the NON NULL values in the above rows ? – Ravi Jan 09 '13 at 16:20
As to your other comment, I've completely reworked my answer. :-) – Itchy Jan 09 '13 at 16:23
First of all, the query generates duplicate rows. I can use Group By rates_Calendar.date to eliminate those. But I do want the amount to remain the same as in my Original Post. But your suggestion updates all of the values to be 138. – Ravi Jan 09 '13 at 16:56
1

Right. You could use my suggested `subQuery` and name it `subQuery2` and add it to the one subquery you had. Then at the beginning you could use `subQuery2` for all but for the one with the 138/0. And don't forget the `GROUP BY`. – Itchy Jan 09 '13 at 17:01
This works at the unit level( I mean unit.unit=221). But when you change it to the property level (property.area = 'London'), the amount stays the same for all of them. I have a LEFT JOIN with a subquery which I use for the amount and the second subquery for the rest of the fields. – Ravi Jan 09 '13 at 18:29
Yes, that's right and makes sense from the part I understand. At the end you should end up with `LEFT JOINS` for all of the "groups" of results. (Sorry for not telling that earlier - but it solved the question you stated. ;-)) – Itchy Jan 09 '13 at 18:52
What do you mean ? Change the second subquery to LEFT JOIN ? – Ravi Jan 09 '13 at 19:54
Yes. If this doesn't work, please also have a look at the answer of Gordon Linoff. By now, I think his solution is more elegant. – Itchy Jan 09 '13 at 20:39

score 1 · Answer 2 · answered Jan 09 '13 at 16:32

1

Is this what you want?

SELECT rates_Calendar.date, coalesce(subQuery.name, 'unitA'),
        COALESCE(subQuery.amount,0) as amount,
        coalesce(subQuery.reference, 1),
        coalesce(subQuery.property, 'property1')
. . .

Or, do you want to read the values from the column, something like:

select rates_Calendar.date,
       coalesce(subquery.Name, max(SubQuery.name) over ()) as name,
       COALESCE(subQuery.amount,0) as amount,
       coalesce(subQuery.reference, max(subquery.reference) over ()) as reference,
       coalesce(subQuery.property, max(subquery.property) over ()) as property

This gets the maximum value from the column and uses that as the default.

answered Jan 09 '13 at 16:32

Gordon Linoff

1,242,037
58
646
786

Hi Gordon, this is not what I meant. Hope you have seen the above conversation. – Ravi Jan 09 '13 at 20:42
@Ravi . . . Actually, it seems to me that the second query *does* answer your question. How is it different from what you want? – Gordon Linoff Jan 09 '13 at 20:45
Second query will answer my question if it does not fetch NULL as the max value. It does not work. It does not pick the max value of the column. SELECT rates_Calendar.date, COALESCE(subQuery.id,max(subQuery.id)), COALESCE(subQuery.region,max(subQuery.region)), COALESCE(subQuery.building,max(subQuery.building)), COALESCE(subQuery.title,max(subQuery.title)), COALESCE(subQuery.name,max(subQuery.name)), COALESCE(subQuery.amount,max(subQuery.amount)) – Ravi Jan 09 '13 at 23:01
@Ravi . . . The only way it would fetch NULL as the max value is when all the values are NULL. In that case, you have nothing to fill in. – Gordon Linoff Jan 10 '13 at 00:04
I do not mean that all values are NULL. The output stays the same as in my original post using max. – Ravi Jan 10 '13 at 10:01
1

@Ravi . . . Yes, this has to do with grouping. The syntax would be a bit different in that case. – Gordon Linoff Jan 10 '13 at 14:34
Can you suggest how would I need to change it ? – Ravi Jan 10 '13 at 15:42

score 0 · Answer 3 · answered Jan 09 '13 at 16:13

0

May be you need IFNULL function?

select ifnull(some_field, 'default_value') from mytable

answered Jan 09 '13 at 16:13

heximal

10,327
5
46
69

There is nothing like a default in the above case. It needs to be unitA in the entire second column, 1 in the entire 4th column and property1 in the entire 5th column. – Ravi Jan 09 '13 at 16:14

score 0 · Answer 4 · answered Jan 21 '13 at 12:59

0

I managed to do this by using a CROSS JOIN ON rates_Calendar and unit tables and joining the subquery on these two tables.

answered Jan 21 '13 at 12:59

Ravi

2,472
3
20
26

Populating NULLS from LEFT JOIN with values

4 Answers4