Return All Records in a Query With Some Fields Blank (1 Viewer)

[JJFernandez]

Hi all,

I apologize if this has been asked and answered but I have put an honest effort in to find an answer with no luck.

I'm trying to run a query and return records for all "incidents", even when some fields I am interested in are blank (actually a record does not exist).

In this specific case, I would like to see all incidents, the incident type, the Investigating enforcement officer, and a suspect's first and last name. Some of the incidents do not have a suspect. I'd still like to see the incident ID, and investigating officer even when no suspect is tied to the incident and I'm unable to currently.

I've tried using criteria but I'm getting no values returned, I assume because the record for a suspect does not actually exist so saying the field is null doesn't really make sense.

I've attached a screen shot if anyone has an idea of what I'm doing wrong, or if I'm designing the query incorrectly, I'd appreciate it.

 

[jdraw]

See this link https://www.w3schools.com/sql/sql_join.asp for more info on JOINS.
They have a sample area for you to experiment.

Good luck.

 

[JJFernandez]

This looks like what I need, thanks for your assistance!

 

[June7]

All links appear to be INNER JOIN. Probably need to change at least one of them to LEFT or RIGHT. You want all the records from IRF_Enforcer_Pointer?

If you want to provide db for analysis, follow instructions at bottom of my post.

 

[JJFernandez]

Thanks for your reply. I'd actually like all records from the table IRF, but you're correct as that would match the IRF_Enforcer_Pointer results.

I will try mess around with the join types and if I'm still stuck I will post a sample db.

Thanks again.

 

[theDBguy]

Hello. This is probably redundant information at this point, but maybe take a look at this article as well. Query Join Basics. Cheers!

 

[JJFernandez]

For the record, I was getting an error about ambiguous outer joins if I simply tried to define all joins at once.

What I had to do was first have individual queries between IRF_Suspect_Pointer and Suspect_Info; as well as IRF_Enforcer_Pointer and Enforcement Officers. I then created the main query with the IRF being the Root (left side of join) and then joined that table to the results of the two sub-queries.