Query Puzzle

[Bradley]

Hi

I have 4 separate tables of which all have fields "Surname", "Forename" and
"DOB", plus the data column.

Tbl_0 = S | F | DOB (this is all 5 users)
Tbl_1 = S | F | DOB | Data1
Tbl_2 = S | F | DOB | Data2
Tbl_3 = S | F | DOB | Data3

Index above = S (Surname)| F (Forename) | DOB | Data

I can build individual queries like;
Tbl_0 & 1 which lists all the Users that have a Data1 enter - Totaling 1
Tbl_0 & 2 which lists all the Users that have a Data2 enter - Totaling 2
Tbl_0 & 3 which lists all the Users that have a Data3 enter - Totaling 2

My problem is that I want to see them all in one table.
For Example;

Users | Data1 | Data2 | Data3
-----------------------------------
Joe text - -
Bill - text text
John - - text
Bob - text -
Peter - - -

Peter should not even show in the list above, because he doesn't have any
data to display.

If this is possible how should I build the Query?

Regards
Bradley

 

[Chaim]

Something sounds wrong here.

Are all of the tables capturing essentially the same information? It sounds
like you are using the table itself as data. The fact that the information
is about person X should be captured in rows in a table, not as a separate
table. If the DataN fields are how you are differentiating your records,
then you should probably have a column that indicates the type of record
this is and put all of them in a single table.

This doesn't answer your question, but may save you much grief down the road
(and not too far down that road!)

Good Luck!

 

[Marshall Barton]

I have 4 separate tables of which all have fields "Surname", "Forename" and
"DOB", plus the data column.

Tbl_0 = S | F | DOB (this is all 5 users)
Tbl_1 = S | F | DOB | Data1
Tbl_2 = S | F | DOB | Data2
Tbl_3 = S | F | DOB | Data3

Index above = S (Surname)| F (Forename) | DOB | Data

I can build individual queries like;
Tbl_0 & 1 which lists all the Users that have a Data1 enter - Totaling 1
Tbl_0 & 2 which lists all the Users that have a Data2 enter - Totaling 2
Tbl_0 & 3 which lists all the Users that have a Data3 enter - Totaling 2

My problem is that I want to see them all in one table.
For Example;

Users | Data1 | Data2 | Data3
-----------------------------------
Joe text - -
Bill - text text
John - - text
Bob - text -
Peter - - -

Peter should not even show in the list above, because he doesn't have any
data to display.

Looks like an unnormalized mess to me. This should be don
with two tables, one for the names and one additional table
for the data.

However, in this specific case (not in general), I think you
can work around the difficulties of the relational database
rules violations with something like:

SELECT F, Data1, Data2, Data3
FROM ((tbl0 LEFT JOIN tbl1
ON tbl0.S = tbl1.S and tbl0.F = tbl1.F)
LEFT JOIN tbl2
ON tbl0.S = tbl2.S and tbl0.F = tbl2.F)
LEFT JOIN tbl3
ON tbl0.S = tbl3.S and tbl0.F = tbl3.F
WHERE Data1 Is Not Null
OR Data2 Is Not Null
OR Data3 Is Not Null