SQL server how to do string comparison with left join

I have two tables: the entry table (one nvarchar column called entry) and the disease table (one nvarchar column called disease).

I would like to produce another table that has all the entry-disease combos where entry.entry is contained completely in disease.disease. However, I want all entries that do not have a disease that is completely contained inside of them to still appear in the results table as {entry, blank}.

I know it should probably be something like:

select entry disease from entry, disease where …

not really sure how to write this, thanks in advance

Ok, I figured out this much:

select entry.entry, disease.disease
into new_table
from entry, disease
where CHARINDEX(entry, disease) > 0

how do I include the entries that have no match?

Answer

You can use left join in this case.
It will show all entries and provide null value for the column disease.disease if an entry is not contained in any cell of disease.disease.

SELECT entry.entry, disease.disease
FROM entry LEFT JOIN disease
    ON CHARINDEX(entry.entry, disease.disease) > 0

Advertisement

Answer