Select if or Case column is empty

Question

I have a table "messages"

['id', 'en', 'type', 'company_id']

i have two types

['new_contract', 'new_offer']

i may have two records with the same 'type' = 'type' but one with a company_id and one with a null 'company_id'

i want to select all records with company_id =1 but if one type is missing for this company get the missing type record even if it's null company_id

 record 1 ['1', 'new contract', 'new_contract','1']
 record 2 ['2', 'new offer', 'new_offer','2']
 record 3 ['3', 'new offer', 'new_offer',NULL]
 record 4 ['4', 'new contract', 'new_contract',NULL]

 Output
 record 1 ['1', 'new contract', 'new_contract', '1']
 record 3 ['3', 'new offer', 'new_offer', NULL]

Not sure if that's possible using sql your text

score 1 · Answer 1 · edited May 26 '23 at 22:18

This reads like a prioritization question, where, for each type, you prefer the row of company 1 over the row where company is null.

One option uses window functions:

select *
from (
    select m.*, 
        row_number() over(partition by type order by company_id desc) rn
    from messages m
    where company_id = 1 or company_id is null
) m
where rn = 1

In the window function, order by company_id desc puts null values last - so this prioritizes the "other" type row, if any.

score 0 · Answer 2 · answered May 26 '23 at 16:22

To get all records of the company_id (if we have multiple rows of the same type) + the null records if there is a missing type, we can do something like this:

SELECT * 
FROM table
WHERE company_id=1 
    OR (company_id IS NULL AND type IS NOT IN (SELECT DISTINCT(type) FROM table WHERE company_id=1))

Hogan · Answer 3 · 2023-05-26T20:16:40.850

0

Just to show another option beside @GMB use of windowing functions -- you take all the items with null and all those without and union them to get a list you want (union will remove duplicates) like

SELECT id, type
FROM messages
WHERE company = 1

UNION

SELECT id, type
FROM messages
WHERE company is null

--

In the comments @islam Elbanna points out that you want to make sure you get all the types. If this is the case then you just select all the types -- that would look like this

 SELECT DISTINCT id, type
 FROM messages

If you also want to know which ones have a entry for company 1 then you can do this

 SELECT base.id, base.type, c.company
 FROM (
   SELECT DISTINCT id, type
   FROM messages
 ) as base
 LEFT JOIN messages as C on base.id = C.id and C.company = 1

edited May 26 '23 at 20:16

answered May 26 '23 at 16:28

Hogan

69,564
10
76
117

AFAIU, returning the null records is only in case there is a missing type for this company_id – Islam Elbanna May 26 '23 at 16:47
@IslamElbanna -- good point -- I added some additional queries if that is the case. – Hogan May 26 '23 at 20:17

score 0 · Answer 4 · answered May 26 '23 at 23:56

0

With this subquery, we will first obtain the company's type as well as any additional type that has not been assigned to a company:

select type, max(company_id) as company_id
from messages      
where company_id = 1 or company_id is null
group by type

Then we join it to the table to get the complete row :

select m.*
from messages m
inner join (
  select type, max(company_id) as company_id
  from messages
  where company_id = 1 or company_id is null
  group by type
) as s on s.type = m.type and s.company_id <=> m.company_id

Demo here

answered May 26 '23 at 23:56

SelVazi

10,028
2
13
29

Thanks, @SelVazi Could you please explain why and s.company_id <=> m.company_id ? – Bisher May 27 '23 at 13:52
and why max(company_id) ? – Bisher May 27 '23 at 14:05
When the company is null, then NULL <=> NULL will yield to true, allowing the join to be matched and the NULL company row to be taken. https://stackoverflow.com/questions/21927117/what-is-this-operator-in-mysql#:~:text=is%20MySQL's%20null%2D,if%20one%20operand%20is%20NULL. – SelVazi May 27 '23 at 14:58
And because we are grouping by type, the company should be taken using max()/min() aggregate function, the max(company) will be 1 or null. – SelVazi May 27 '23 at 15:00

Select if or Case column is empty

4 Answers4