Matching values on 3 tables but retaining nulls 1

[scriptscribe]

Hi,

I have three tables all of which have names and numbers in them in corresponding rows. I want a query to match on the numbers of all three tables, while also returning the nulls in all three tables. I'm doing the comparison between the three tables because I need to know which tables need the names and numbers added and need to display them all on one spreadsheet.

Here are examples of my tables:

Table1
Smith,John 51111
Donner,Jane 52222
Jones,David 53333
Johnson,Susie 54444

Table2
Smith,John 51111
null
Jones,David 53333
null

Table3
null
Donner,Jane 52222
null
Johnson,Susie 54444

I want the results to look like this:

Table4

Smith,John    51111   Smith,John   51111  null
Donner,Jane   52222   null                Donner,Jane   52222
Jones,David   53333   Jones,David  53333  null
Johnson,Susie_54444   null                Johnson,Susie 54444

I know how to do a left outer join and a basic inner join, but am lost about how to approach this.

Can anyone give me a hand?

Thanks,
Mike

 

[JBinQLD]

G'day fella,

Look on this forum for posts about normalisation. If the three tables were normalised you could easily create a report and use the sorting and grouping features to produce that output

The crux of the problem is, you don't want to be potentially storing the same name and employee number in 3 tables.

Good luck!

JB

 

[scriptscribe]

Actually, JBinQLD, that's exactly what I need (same name and number) all on the same table. I will only be using this table to export to excel for a presentation of which databases are missing entries for certain employees. When I got to do the actual load, I won't be using this table to do so, so I should be safe.

Thanks for the tip on normalization.

Mike

 

[scriptscribe]

Hi,

Any other suggestions about how to go about this? I wasn't able to find anything under "Normalization" that seemed to help.

Thanks,
Michael

 

[JBinQLD]

G'day Michael,

It's easy enough to do, just wasn't sure of the reasoning behind it. As it's a one off...

SELECT Table1.PersonName, Table1.empnum, Table2.PersonName, Table2.empnum, Table3.PersonName, Table3.empnum
FROM (Table1 LEFT JOIN Table2 ON Table1.PersonName= Table2.PersonName) LEFT JOIN Table3 ON Table1.PersonName= Table3.PersonName;

SQL of course assumes Table1, Table2, Table3 which in my case consisted of

PersonName - Text
Empnum - number

...to mimic your example

Have a great weekend,

JB