Question 176082 in Mysql Question posted in
The official MySQL documentation can be found here.

Is the column empty in MySQL SELECT IF or CASE?

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

Tags:

3

Answers


  1. 0

    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
    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.

    Login or Signup to reply.
  2. 0

    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))
    Login or Signup to reply.
  3. 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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search