Join with null values (1 Viewer)

Tskutnik · Feb 10, 2018

Hey all,

My query has multi-field joins between 2 tables. Is it possible to include Null values on both sides of the joined tables as equal values in one of the joins, so the records relate?

In this case the first join is on actual values (the normal way), but the second could have null on both sides, which I'd want to relate as equal.

Thanks

boerbende · Feb 10, 2018

I think you'll have to look into "left join" or "right join" queries

isladogs · Feb 10, 2018

Tskutnik said:
Hey all,

My query has multi-field joins between 2 tables. Is it possible to include Null values on both sides of the joined tables as equal values in one of the joins, so the records relate?

In this case the first join is on actual values (the normal way), but the second could have null on both sides, which I'd want to relate as equal.

Thanks

As already described by Ben, use outer joins.
However you need to be aware that Null doesn't equal anything - not even another null

Try typing this in the VBE immediate window
?1=1
The result is true (obviously)

?2=1 gives False

?Null=Null
The result is Null

Pat Hartman · Feb 10, 2018

This is indicative of a design flaw. The multi-fields should probably be in separate tables since they are optional.

However, if you are not prepared to redesign, you might be able to solve the problem by changing the datatype of the optional fields to text. You also have to make the default ZLS rather than null and when you append data, convert any null values to ZLS.

As has already been mentioned, Null is nothing and Null = Null returns null. Since, ZLS is something that means ZLS will = ZLS allowing you to join the two fields.

Mark_ · Feb 11, 2018

@Pat,

I take it ZLS = "Zero Length String", or ""?

Pat Hartman · Feb 12, 2018

Yes. ZLS = Zero Length String. It is coded as --- """ ---- two double quotes with nothing between them and is different from Null.

isladogs · Feb 13, 2018

Pat Hartman said:
Yes. ZLS = Zero Length String. It is coded as --- """ ---- two double quotes with nothing between them and is different from Null.

Methinks you were a little too keen typing double quotes as I can see three!

Pat Hartman · Feb 13, 2018

Thanks Colin. Apparently I have developed a stutter.

Mark_ · Feb 13, 2018

Just trying to cut down on the unexplained MLAs floating around here. I wrote a paper on proper use of MLAs a while back.

B-) Just so anyone who's really interested knows, MLA = Multi Letter Acronym.

isladogs · Feb 13, 2018

LOL. You were the person who explained what OIC stood for.

Great site explaining MLAs here https://www.acronymfinder.com

Mark_ · Feb 13, 2018

@Colin,

Rather than risk having anyone bother the Officer in Charge, I thought it prudent to explain his meaning...

isladogs · Feb 14, 2018

At the time, I wondered if he was calling me an oick....!

CJ_London · Feb 14, 2018

From Colins link I see ZLS also stands for Zero Lifetime Space - now does anyone want to explain what that is?

isladogs · Feb 14, 2018

It means spending so long on forums answering questions that you don't have time to develop your own applications....

Mark_ · Feb 14, 2018

Or have one of these so called "Lives"?

Join with null values (1 Viewer)

Tskutnik

Registered User.

boerbende

Ben

isladogs

MVP / VIP

Pat Hartman

Super Moderator

Mark_

Longboard on the internet

Pat Hartman

Super Moderator

isladogs

MVP / VIP

Pat Hartman

Super Moderator

Mark_

Longboard on the internet

isladogs

MVP / VIP

Mark_

Longboard on the internet

isladogs

MVP / VIP

CJ_London

Super Moderator

isladogs

MVP / VIP

Mark_

Longboard on the internet

Similar threads

Users who are viewing this thread