How to add where on column which is selected null

Question

I am merging 2 table data in the query to show with pagination but for filter in second query I dont have client name in second query but when i filter the data second table data appears so i want to add not null but getting unknown column error.

(
select 
    `linguists`.`id` as `linguist_id`, 
    CONCAT(linguists.first_name," ", linguists.last_name) AS linguist_name, 
    `reps`.`id` as `rep_id`, 
    CONCAT(reps.first_name, " ", reps.last_name) AS rep_name, 
    `clients`.`id` as `client_id`, 
    `clients`.`name` as `client_name`, 
    `clients`.`type` as `client_type`, 
    `invoices`.`booking_id`, 
    NULL AS id, 
    `bookings`.`booking_date`, 
    `invoices`.`client_paid`, 
    `invoices`.`linguist_paid`, 
    `invoices`.`demand_letter_id`, 
    `invoices`.`reminder_1`, 
    `invoices`.`reminder_2`, 
    `invoices`.`reminder_3`,
    `invoices`.`reminder_4`, 
    `invoices`.`exceptional_case`, 
    bookings.source_company AS scope, 
    `bookings`.`credit_note`, 
    DATEDIFF(CURDATE(), invoices.created_at) AS days_since_invoice, 
    NULL AS start_date, 
    NULL AS end_date 
from `invoices` 
left join `bookings` on `bookings`.`id` = `invoices`.`booking_id` 
left join `clients` on `clients`.`id` = `bookings`.`client_id` 
left join `reps` on `reps`.`id` = `bookings`.`rep_id` 
left join `linguists` on `linguists`.`id` = `bookings`.`linguist_id` 
where 
    (
        `clients`.`name` like '%t%' 
    or 
        `clients`.`id` = 't'
    ) 
and `invoices`.`linguist_paid` is null 
and `invoices`.`invoice_submitted_linguist` < '2023-04-12' 
and `bookings`.`source_company` = 'UKLS'
) 

union all 
(
 select 
    `linguist_id`, 
    CONCAT(linguists.first_name, " ", linguists.last_name) AS linguist_name, 
    NULL AS rep_id, 
    NULL AS rep_name, 
    NULL AS client_id, 
    NULL AS client_name, 
    NULL AS client_type, 
    `on_demand_linguist_invoice`.`id`, 
    NULL AS booking_id, 
    NULL AS booking_date, 
    NULL AS client_paid, 
    `linguist_paid`, 
    NULL AS demand_letter_id, 
    NULL AS reminder_1, 
    NULL AS reminder_2, 
    NULL AS reminder_3, 
    NULL AS reminder_4, 
    NULL AS exceptional_case, 
    NULL AS scope, 
    NULL AS credit_note, 
    DATEDIFF(CURDATE(), on_demand_linguist_invoice.created_at) AS days_since_invoice, 
    `on_demand_linguist_invoice`.`start_date`, 
    `on_demand_linguist_invoice`.`end_date` 
from `on_demand_linguist_invoice` 
left join `linguists` on `linguists`.`id` = `on_demand_linguist_invoice`.`linguist_id` 
where 
    (
        client_name is not null
    ) 
    and `on_demand_linguist_invoice`.`linguist_paid` is null 
    and `on_demand_linguist_invoice`.`invoice_submitted_linguist` < '2023-04-12'
)

I tried using where client_name is not null so the second table data will not appear because i selected NULL as client_name in select but getting Unknown column 'client_name' in 'where clause'

score 1 · Accepted Answer · answered May 12 '23 at 10:56

i selected NULL as client_name in select but getting Unknown column 'client_name' in 'where clause'

client_name is an alias of the output column. But you cannot use output column name in WHERE because it does not exist on this step of the query execution.

You may either use the output column expression instead the alias (in your case this will be WHERE NULL IS NULL) or move this condition to HAVING clause (in your case this will be WHERE ... HAVING client_name IS NULL)

How to add where on column which is selected null

1 Answers1