Results 1 to 14 of 14
  1. #1
    Join Date
    Aug 2003
    Location
    AUS
    Posts
    26

    Unanswered: 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
    Melb

  2. #2
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    to 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.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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!
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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!!!!!!
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    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

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rdjabarov
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Aug 2003
    Location
    AUS
    Posts
    26
    Thanks guys
    Have compromised the DB designer and have recreated the tables in different format.

    cheers
    Melb

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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)
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rdjabarov
    Rudy, I never said the design was "far from normalized
    i know you didn't, but blindman did, and you said you "have to go with [his] comment"

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    OK, what I refered to was this:
    Quote Originally Posted by BLindman
    but FirstName/Age/City as a composite primary key?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  13. #13
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    And BTW, this combination CANNOT POSSIBLY YIELD a unique primary key, which means the design ain't worth you know what either...you know?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •