How to I map null/duplicate results from my SQL query. I am new to using google cloud platform and using unions (although I understand that with unions the select statements must match).

These are the results form my query

Client ID	Staff	appointment status	date of service	appointment date
1	HILL,PEGGY	No Show	NULL	11/02/2022
2	HILL,PEGGY	Scheduled	NULL	11/05/2022
2	HILL,PEGGY	NULL	11/05/2022	NULL
3	HILL,PEGGY	NULL	11/10/2022	NULL
3	HILL,PEGGY	Scheduled	NULL	11/10/2022

As you can see the ClientID of 2 has a duplicate but the appointment status and date are null, I want to get rid of all the null values so the desired results would be this:

Client ID	Staff	Appointment Status	Date of Service	Appointment Date
1	HILL,PEGGY	No Show	11/02/2022	11/02/2022
2	HILL,PEGGY	Scheduled	11/05/2022	11/05/2022
3	HILL,PEGGY	Scheduled	11/10/2022	11/10/2022

This is my SQL query:

select

select clientID

, staff_name

, appointment_status_value Status

, COALESCE(srvDate, apptDate) AS DateOfSrv

, COALESCE(apptDate,srvDate) AS ApptDate

from (

select clientID

, staff_name

, appointment_status_value Status

, 'billing.date_of_service' srvDate

, FORMAT_DATETIME("%Y-%m-%d", appointment.appointment_date) apptDate

from billing_table

and cast(date_of_service as date) between '2022-11-01' and current_date()

UNION DISTINCT

select clientID

, staff_name

, appointment_status_value Status

, 'billing.date_of_service' srvDate

, FORMAT_DATETIME("%Y-%m-%d", appointment.appointment_date) apptDate

FROM appointment_table

where cast (appointment_date as date) between '2022-11-01' and current_date()

order by clientID

)

I am really REALLY struggling with this so any help would be greatly appreciated.