CODEWITHSUNDEEP
mysqlsql
grabbag
grabbag

Reputation: 1030

Left join where left table column = something or null

I need to join a left table with the right table, and keep all left table rows even if right table does not have a match. ... a standard left join.

Include only left table rows meeting a criteria.

Include only right table rows meeting a criteria. If none is found, use null in the join.

Sort by column in right table, if not null, but don't loose entry if null.

in psudo sql, I want

(
    select
        *
    from
        work_item
    where
        work_item.id_work_item_queue = 5
) as t1

(
    select
        *
    from
        ai_guess
    where
        ai_guess.AI_Guess_Date = '2019:08:09:19:49:02'
) as t2

select
    t1.*,
    t2.*
from 
    t1
    left join t2 on t1.id_word_crop = t2.id_word_crop
ORDER BY
    ai_guess.text

The closes I come to a solution is 

SELECT 
    work_item.id_work_item,
    work_item.id_word_crop,
    ai_guess.text

FROM
    work_item
    LEFT JOIN ai_guess ON
    work_item.id_word_crop = ai_guess.id_word_crop

WHERE
    work_item.id_work_item_queue = 5
    AND
    ai_guess.AI_Guess_Date = '2019:08:09:19:49:02'

ORDER BY
    ai_guess.text

This generates 

id_work_item, id_word_crop, text
'4', '224343', 'eie'
'3', '224342', 'geüichrt'
'2', '224341', 'rschaunig'
'6', '224345', 'VSnge'

but it's missing and entry from the left table

id_work_item, id_word_crop
'5', '224344'

The left table contains 

SELECT
    id_work_item,
    id_word_crop
FROM
    work_item
where
    id_work_item_queue = 5

    id_work_item, id_word_crop
    '2', '224341'
    '3', '224342'
    '4', '224343'
    '5', '224344'
    '6', '224345'

The right table does not have a match so the left entry is dropped but I want to keep the left entry.

SELECT
    *
FROM
    ai_guess
where
    id_word_crop = 224344

returns null

Upvotes: 0

Views: 58

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

Move the criteria on the ai_guess table from the WHERE to ON clause:

SELECT
    work_item.id_work_item,
    work_item.id_word_crop,
    ai_guess.text
FROM work_item
LEFT JOIN ai_guess
    ON work_item.id_word_crop = ai_guess.id_word_crop AND
       ai_guess.AI_Guess_Date = '2019:08:09:19:49:02'
WHERE
    work_item.id_work_item_queue = 5
ORDER BY
    ai_guess.text;

What is happening now is that the join generates the intermediate result you want, but then the WHERE clause is filtering off the missing record because its guess date does not match. By placing this restriction in the join criteria it prevents that record from being removed prematurely.

Upvotes: 2

Related Questions