Joining three tables

G'day,
I have got following 4 tables

Table 1
name age city

jack 20 Melbourne
Nick 30 Bendigo
Russ 28 Sydney

Table 2
name age city Company

jack 20 Melbourne AAA
Nick 30 Bendigo BBB
Russ 28 Sydney AAA
Marty 31 Perth AAA

Table 3

name age city Position

jack 20 Melbourne Manager
Nick 30 Bendigo Manager
Russ 28 Sydney Clerk
Marty 31 Perth Manager

Table 4

name age city datejoined

jack 20 Melbourne 09-09-2001
Nick 30 Bendigo 08-05-2001
Russ 28 Sydney 10-12-2000
Marty 31 Perth 11-11-1999

I want a query which extract the name, age and city from Table 2 (where name,age and city equals table1 values) and position from table3 where position is 'manager' else return null and date joined from table 4 only for the managers else return null.

so the result should be

name age city position datejoined

jack 20 Melbourne Manager 09-09-2001
Nick 30 Bendigo Manager 08-05-2001
Russ 28 Sydney null null

my query

SELECT b. name, b.age, b.city,b.company,c.position,d.datejoined
FROM Table1 a, Table2 b, Table3 c, Table4 d
WHERE
a.age=b.age
and a.name=b.name
and a.city=b.city
and b.age*=c.age
and b.name*=c.name
and b.city*=c.city
and b.position='Manager'
and b.age*=d.age
and b.name*=d.name
and b.city*=d.city

THE RESULT IS

jack 20 Melbourne Manager 09-09-2001
Nick 30 Bendigo Manager 08-05-2001
Russ 28 Sydney null 10-12-2000

When I try to join table4 with table i am getting a exception

Ps: as the original code was in SQL SERVER 6.5 I have to use *= for joins not keywords LEFT JOIN or RIGHT JOIN

hope yo guys can help me

regards
Melbto solve it your way gives me a headache...so

here is my alternate solution
combine all of the tables into one table

name age city position company datejoined
joe 20 melbourne manager xyz co. 8/15/2000

this would move you up to at least 2NF
then add a primary key to uniquely identify the entity
such as employeeid or EmpId
then add a column called ReportsTO

empid name age city position company datejoined reportsto
1 joe 20 melbourne manager xyz co. 8/15/2000 1
2 mary 25 perth stooge xyz co. 10/15/2003 1
3 steve 25 perth stooge xyz co. 11/15/2003 2

then join the table to itself.

select name, age, city, position, company, datejoined
from tablea as TA join tablea as TB
on ta.reportsto = TB.empid
where position = 'manager'

check out the employees table in northwind for the direct example.
and ps
always set the top most employee's reportsto column eaqual to that employees own empid
this eliminates the need for an outer join in the self join.|||Look, here's the logic required. Translate it into archaic WHERE joins if you must...

select table1.name, table1.age, table1.city, table2.Company, table3.Position, table4.datejoined
from table1
inner join table2
on table1.name = table2.name
and table1.age = table2.age
and table1.city = table2.city
left outer join table3
on table1.name = table3.name
and table1.age = table3.age
and table1.city = table3.city
and table3.Position = 'Manager'
left outer join table4
on table3.name = table4.name
and table3.age = table4.age
and table3.city = table4.city

...but I gotta tell you this is one screwed up table structure. Not only is far from normalized, but FirstName/Age/City as a composite primary key?|||Man, once the original question reached the end of the screen I lost track and interest...But for Codd's sake, I hope the designer of this database is in prison by now for systems analysis and database design fraud!|||ease up guys

that table structure is in 5th normal form

the only thing it doesn't do is utilize surrogate keys, and that in itself is not a crime

assuming table 1 has a composite primary key, then tables 2, 3, and 4 are single value attribute tables

notice that in this design, nulls are not required, so that if if a given person doesn't have a company, position, or date joined, then there won't be a row for that person in the associated table

perfectly valid design, and you can ask the guys at dbdebumph.com to verify this

not that you'd ever catch me creating a design like this, because, unlike the guys at dbdebumph.com, i *heart* nulls

the complex left outer joins is a perfect example of why this type of design sucks

yes, it's an egregiously impractical design, but from a normalization point of view, it is fine

normalization does not mean "remove primary key redundancy across tables by implementing surrogate keys if you can"

normalization has to do with the dependency of non-key attributes on the primary key and on interdependence on non-key attributes

disclaimer: i'm not 100% certain about it being 5th normal, maybe it's only 4th normal, but i know it's higher than 3rd|||Come ON, Rudy, how can you say that NAME, CITY, and AGE be perfect for a primary key?????? I HAVE to go with BLindman's comment on that!!!!!!|||now here is a perfect example of contradictory motives and operations

it is entirely possible to assume as most of us did that this was de-normalized data, and not a fully realized model.
however the "glass is half full" crowd have decided that this example is so far along in it's design that it is close to the idiot savant stage.

our motive to achieve cleanliness in our data design can sometimes cause us to chase operations that contradict common sense.

so it seems that it is entirely possible to follow the Rules just so far that you actually break all of the rules.

this is why i consider normalization to be a good GUIDELINE but not a hard and fast rule. i have always maintained that it is the business model that is being exposed by appropriately identifying the business rules that govern the company. and accurately define the data model as well
or simply business drives data.

Scott Davis
"If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..." Mr. Wolf|||Come ON, Rudy, how can you say that NAME, CITY, and AGE be perfect for a primary key?????? I HAVE to go with BLindman's comment on that!!!!!!did i say it was a perfect primary key? no, i said it was a perfectly valid design

and which comment of blindman's are you going with?

if it's "screwed up table structure" then maybe i agree, because as we all seem to realize, a structure using a surrogate key might be a lot better

but if the comment you go with is "far from normalized" then you are wrong

like i suggested, you guys need to look up the definition of the normal forms before you start claiming something is not normalized|||Thanks guys
Have compromised the DB designer and have recreated the tables in different format.

cheers
Melb|||Rudy, I never said the design was "far from normalized", I just said that I ho[ped the designer was somehow prosecuted for DBA fraud, or something like that!

I KNOW you know what you're talking about, but I hope the guy (poster) drew a lesson from all this, that's all (hands up, hoping for being released to the family for database police cooperation) ;)|||Rudy, I never said the design was "far from normalizedi know you didn't, but blindman did, and you said you "have to go with [his] comment"

:)|||OK, what I refered to was this:but FirstName/Age/City as a composite primary key?|||And BTW, this combination CANNOT POSSIBLY YIELD a unique primary key, which means the design ain't worth you know what either...you know?|||what do you mean, "CANNOT POSSIBLY YIELD a unique primary key"??

of course it can

it's not very practical, but it is most certainly possible