Is it possible to join even though there is a null entry?

Question

I have two table that I am trying to join together. Proposed cost and Project. My join works as expected except for one entry where the Concept_ID_DFF (Project) used to join is null. Is there a way that I can join even though its blank.

Table (Project)

SRC_START_DTTM	Concept_ID_DFF
2021-09-20 18:53:56.003	NULL
2021-09-20 18:54:22.150	300000005876166
2021-09-23 14:02:49.000	300000005876166

Table (Proposal)

CST_AMT	Concept_ID_DFF
1262450.00	300000005876166
1510546.00	300000005876166
12874.00	300000005876166

Query Result

~Project	center	right
23241	2021-09-20 18:54:22.150	1262450.00
23241	2021-09-20 18:54:22.150	1510546.00
23241	2021-09-20 18:54:22.150	12874.00
23241.0001	2021-09-23 14:02:49.000	1262450.00
23241.0001	2021-09-23 14:02:49.000	1510546.00
23241.0001	2021-09-23 14:02:49.000	12874.00

In short for query result I want to see another 3 row that is with date 2021-09-20 18:53:56.003, but this is coming in null. Is there a way to have it be 300000005876166. I expect to see 23241, 23241.0001, and 23241.0002. Is there a way that I can fix my code to compensate for the null value?

SELECT  CONVERT(bigint, FACT_IM_TPE_PROPOSL_COST.CONCEPT_MASTER_DSID) AS [~Proposal]
    ,   FACT_IM_TPE_PROPOSL_COST.VERSION AS [Cost Version]
    ,   DATEFROMPARTS(TRY_CONVERT(int, FACT_IM_TPE_PROPOSL_COST.CST_YEAR), 1, 1) AS [~Reporting Period]
    ,   DIM_PRJ.SRC_START_DTTM
    ,   DIM_PRJ.PRJ_STAT_CD
    ,   DIM_PRJ.CONCEPT_ID_DFF
    ,   DIM_PRJ.DWID
    ,   CONVERT(float, (DIM_PRJ.DWID + (ROW_NUMBER() OVER (PARTITION BY DIM_PRJ.DWID, FACT_IM_TPE_PROPOSL_COST.CST_AMT  ORDER BY DIM_PRJ.SRC_END_DTTM, TRY_CONVERT(bigint, DIM_PRJ.PRJ_STAT_CD))*.0001)) - .0001) AS [~Project]
    ,   TRY_CONVERT(int, FACT_IM_TPE_PROPOSL_COST.CST_YEAR) AS [Cost Year]
    ,   FACT_IM_TPE_PROPOSL_COST.CST_AMT AS [Proposed Cost] 
FROM Financial_Repository.FACT_IM_TPE_PROPOSL_COST
    LEFT OUTER JOIN Financial_Repository.DIM_PRJ_HISTORY DIM_PRJ
        ON DIM_PRJ.CONCEPT_ID_DFF = FACT_IM_TPE_PROPOSL_COST.CONCEPT_MASTER_DSID
WHERE DIM_PRJ.DWID = '23241' AND [Version] = '1'
ORDER BY [~Project] ASC

Why is it null? Seems like it shouldn't be to me, the record of data is basically only a random date otherwise. — Timothy G., Sep 07 '22 at 15:54
The Concept_ID_DFF is your only joining column but you could just add `or Concept_ID_DFF is null` to the join; you're removing the outer-joined rows anyway in the where clause. — Stu, Sep 07 '22 at 16:04
What should happen when you have different values for `Concept_ID_DFF`? When, i.e., this `Concept_ID_DFF` can also have the value `300000005876167` ? — Luuk, Sep 07 '22 at 16:09
Yeah concept_ID_DFF varies so the code wont work, Im just gonna have to figure out why its coming in null. — Vince Mack, Sep 07 '22 at 16:18
What does "coming in null" mean? "even though it's blank" What does that mean? If you mean it is (the value) null, say so. — philipxy, Sep 08 '22 at 02:06
LEFT JOIN returns INNER JOIN rows UNION ALL unmatched left table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN. After a LEFT JOIN a WHERE, INNER JOIN or HAVING that requires a right [sic] table column to be not NULL removes any rows with introduced NULLs, ie leaves only INNER JOIN rows, ie "turns OUTER JOIN into INNER JOIN". You have that. PS This is a faq. But one must pin down via a [mre] & write many clear, concise & precise phrasings of one's question/problem/goal to search reasonably. [A Q&A.](https://stackoverflow.com/q/4752455/3404097) — philipxy, Sep 08 '22 at 02:08

score 0 · Answer 1 · answered Sep 07 '22 at 16:02

0

If I understand the ask you want it to default to 300000005876166 if null. You could put a function into the join to default to 300000005876166 on the project ID DFF columns.

See join below

FROM Financial_Repository.FACT_IM_TPE_PROPOSL_COST
    LEFT OUTER JOIN Financial_Repository.DIM_PRJ_HISTORY DIM_PRJ
     ON ISNULL(DIM_PRJ.CONCEPT_ID_DFF, '300000005876166') = FACT_IM_TPE_PROPOSL_COST.CONCEPT_MASTER_DSID

answered Sep 07 '22 at 16:02

VLOOKUP

548
4
12

1

If that is the case, yes. But if there are other null entries, those too would get this value, which could be incorrect for the data (assuming that those entries should have their own `CONCEPT_ID_DFF` value). But without knowing the data, there's no telling if that is the case or not. But I would imagine it is... – Timothy G. Sep 07 '22 at 16:06
Yeah that would only work for that once conceptID, but I have several. I agree, I should just try to figure out why its coming in as null. Thank you! – Vince Mack Sep 07 '22 at 16:16

score 0 · Answer 2 · answered Sep 07 '22 at 18:41

Use a CROSS JOIN. For example:

select *
from project p
cross join proposal l

Result:

 SRC_START_DTTM           Concept_ID_DFF   SRC_START_DTTM  Concept_ID_DFF  
 ------------------------ ---------------- --------------- --------------- 
 2021-09-20 18:53:56.003  null             1262450.00      300000005876166 
 2021-09-20 18:54:22.150  300000005876166  1262450.00      300000005876166 
 2021-09-23 14:02:49.000  300000005876166  1262450.00      300000005876166 
 2021-09-20 18:53:56.003  null             1510546.00      300000005876166 
 2021-09-20 18:54:22.150  300000005876166  1510546.00      300000005876166 
 2021-09-23 14:02:49.000  300000005876166  1510546.00      300000005876166 
 2021-09-20 18:53:56.003  null             12874.00        300000005876166 
 2021-09-20 18:54:22.150  300000005876166  12874.00        300000005876166 
 2021-09-23 14:02:49.000  300000005876166  12874.00        300000005876166

See running example at db<>fiddle.

Is it possible to join even though there is a null entry?

2 Answers2