Categories
Database Development

How to exclude data if another column is null?

This SQL query returns a column called "organisation_names" however the data is coming from a column called organisation_users and I would like to exclude "organisation_names"

if…
organisation_users.delete_at === null
otherwise return the column

Considering the SQL query below, got any ideas?

I was considering adding column:
GROUP_CONCAT(CONCAT(organisation_users.deleted_at)) as organisation_users_deleted_at

Then excluding it on the query if it has a date…

select
users.*,
user_types.type,
agent_profiles.user_id,
contacts.status,
contact_numbers.number as number,
GROUP_CONCAT(CONCAT(organisations.trading_name, '~', organisations.id)) as organisation_names
from `users` left join `agent_profiles` on `users`.`id` = `agent_profiles`.`user_id` left join `contacts` on `agent_profiles`.`contact_id` = `contacts`.`id` left join `contact_numbers` on `contact_numbers`.`contact_id` = `contacts`.`id` and `contact_numbers`.`primary` = '1' left join `organisation_users` on `organisation_users`.`user_id` = `users`.`id` left join `organisations` on `organisations`.`id` = `organisation_users`.`organisation_id` left join `user_user_types` on `user_user_types`.`user_id` = `users`.`id` left join `user_types` on `user_types`.`id` = `user_user_types`.`user_type_id` where ((`contacts`.`status` is null and `agent_profiles`.`user_id` is not null) or `user_types`.`type` = 'Admin' or (`contacts`.`status` is null and `agent_profiles`.`user_id` is null and `user_types`.`type` != 'Admin') or `contacts`.`status` in ('Active', 'Inactive', 'On-Hold')) and (CONCAT(users.first_name, ' ', users.last_name) LIKE '%john smith%' or email LIKE '%john smith%' or number LIKE '%john smith%' or user_types.type LIKE '%john smith%' or contacts.status LIKE '%john smith%') and `users`.`deleted_at` is null group by `users`.`id` order by `users`.`first_name` asc limit 10 offset 0

Leave a Reply

Your email address will not be published. Required fields are marked *