Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2008
    Posts
    4

    Unanswered: Joining 3 tables with natural join

    Hello everybody.

    Take this database schema:
    Classes(class, type, country, numGuns, bore, displacement)
    Ships(name, class, launched)
    Battles(name, date)
    Outcomes(ship, battle, result)

    Find for each class the year in which the last ship of that class was sunk.

    Does following work when I try to natural join Classes Ships and Outcomes?

    SELECT Classes.class, MIN(date)
    FROM Classes NATURAL JOIN Ships NATURAL JOIN Outcomes
    WHERE result = 'sunk'
    GROUP BY Classes.class

    Or would I have to do something as

    FROM Classes NATURAL JOIN Ships CS, CS NATURAL JOIN Outcomes CSO

    And can you give tuple variables to a join, like CS and CSO?

    Kind regards

  2. #2
    Join Date
    Oct 2008
    Posts
    4
    I apologise, should've made this thread in the SQL forums.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    It appears that you've got a very early assignment from a class. Probably your first or second class session would be my guess. Experiment a bit, you don't need to be afraid of breaking things while doing a SELECT!

    Give it a shot. If you have questions about your results, show us what you tried and what result you got. We'll be glad to help, but we won't do your homework for you (if we appear to do your homework for you, be VERY nervous!).

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by boxden View Post
    Ships(name, class, launched)
    Battles(name, date)
    unless the homework assignment specifically requires that you must use NATURAL joins, i would stay well and truly clear of them

    if you should ever accidentally put the Ships and Battles tables into the same query with NATURAL joins, you might be surprised (if that's the word) at the results you get (hint: probably none)

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

  5. #5
    Join Date
    Oct 2008
    Posts
    4
    @ Pat Phelan, I don't really know how to test queries lol What is a free way (accept for MS Access) to create a database schema and use SQL(querying and updating) ?

    @ r937 Yep sorry my bad lol it was no homework assignment, my book is just filled with schema's of ships so I wanted to construct a query so I could ask my question concerning 3 nat joins but I forgot Battles name is the name of the battle :-) Bad example. My actual question is...

    Can you join 3 relations R,S,T in a for clause like

    R NATURAL JOIN S NATURAL JOIN T
    or would you have to do something like
    R NATURAL JOIN S, S NATURAL JOIN T

    I know this would be easy to test if I knew how as I stated in the beginning of this reply.
    I acctually had my computer science exam of introduction to database technologies today and it went very well. Whenever something as joining 3 relations occured, I just joined them in the WHERE clause.

    Kind regards

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by boxden View Post
    I know this would be easy to test if I knew how as I stated in the beginning of this reply.
    most of the more common database systems offer a free version

    probably the easiest to set up and get started with is mysql, but you need to pick one for yourself

    google is yoiur friend to find the download pages for whichever system you choose

    and do heed my advice -- stay well away from NATURAL joins
    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
  •