selecting data where it does or does not appear in a table 2

[jeffwest21]

Having a stupid moment that I hope someone can help me with.

I have a number of tables to pull some data from, two tables in particular, one has an ID that may or may not appear in table 2, the tables are linked via a join where a.con_id = b.Con_id.

How do or I set up my join to pull back data for both those records where it does not exist and where they do, I am sure I have done this before, but as I am trying to update someone else's code, I am trying to not to try and have to rewrite the entire very long winded SP to achieve this.

'Clever boy...'

 

[jeffwest21]

Ok, got this sorted out, it all about the joins that just were not right.

'Clever boy...'

 

[Olaf Doschke]

This is purely a thing on inner vs outer joins. Eg if the tables are 1-to-many (parent-child) linked, a LEFT or RIGHT outer join pulls all parent side records and matching child records, including childless parent records with NULLs on the child side, LEFT or RIGHT just denote the parent side. The typical db developer will have the parent data on the left side.

A FULL OUTER join additionally reveals orphaned records by also joining NULLs to the parent side, when only child data exists.

This also is helpful in case of comparing two equivalent tables eg compare old/new state or existing/import data.

Bye, Olaf.

 

[Auguy]

I'm going to jump in here and give Olaf a star.
I think the Full Outer join point he makes is one I and possibly others have over looked when trying to find orphaned records.

Auguy
Sylvania/Toledo Ohio

 

[jeffwest21]

I am going to give him a star as well, although to be honest as I had already said I had solved it by playing around with the joins until I got them correct, my issue was an old report that someone else had built (a very long time ago), on a poorly designed database that had 23 links through it, so making a change was a nightmare, but got it in the end.

'Clever boy...'

 

[Olaf Doschke]

Thanks to both of you. I see, 23 links, hmm. I don't know, but I'd rather split such reports into main and subreports and let each level have simpler queries.

Bye, Olaf.

 

[johnherman]

The OUTER JOIN concept is important. It's much more efficient than writing code that says where A NOT IN B and B NOT IN A. UNION this UNION ALL that, NOT IN, etc.

==================================
adaptive uber info galaxies (bigger, better, faster, and more adept than cognitive innovative agile big data clouds)