SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict The Mog's Avatar
    Join Date
    Dec 2002
    Location
    Manchester UK
    Posts
    310
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Quick Brain teaser

    ok here goes,

    i have 2 tables in a db

    table 1 = countries

    fields = id
    country


    table 2 = names

    fields = id
    countryid
    name
    etc

    now how would i do the following....


    select all the names in table 2 that are "kenny" but sort them by the "country name asc" from table 1 ??

    any help would be great

    K-

  2. #2
    Sidewalking anode's Avatar
    Join Date
    Mar 2001
    Location
    Philadelphia, US
    Posts
    2,205
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT n.name FROM names n, countries c 
    WHERE n.name = 'kenny'
    ORDER BY c.country ASC
    if I'm not mistaken.
    TuitionFree a free library for the self-taught
    Anode Says... Blogging For Your Pleasure

  3. #3
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT * FROM names
       LEFT JOIN
          ON names.countryid = countries.id
       GROUP BY names.country
    I think...
    Aaron Brazell
    Technosailor



  4. #4
    SitePoint Zealot
    Join Date
    Oct 2002
    Posts
    146
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT * FROM names, countries 
    WHERE names.countryid=countries.id 
    AND names.name='kenny' 
    ORDER BY countries.country
    My take on that one

    [Edit to add line breaks]


    Amorya

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    okay, here's my contribution, you guys can all throw tomatoes at me, i don't care

    the subject was "brain teaser" so there has to be a few "gotchas" in here, intentional or otherwise

    first of all, the request was to select names

    so all you guys that used "select star" are returning too many columns

    this is a nasty habit that results in inefficient applications

    if you "select star" from a table that happens to have a very large column (e.g. LONGVARCHAR), let's say, oh, just a few megs, when all you want is an eensie weensie names column...

    okay, second point -- how sure are you that an inner join is appropriate?

    i would assume that the column called countryid in the names table is a foreign key to the id column in the countries table

    this will tempt you to use an inner join, because you've all learned that foreign keys have to exist in the primary key table, right?

    but in the absence of specific confirming information, that is potentially an error

    the participation of the names entity in the country-names one-to-many relationship might be optional, in which case there could be rows in the names table which have a null foreign key to the countries table, i.e. names that are not related to any country

    if that is the case, then an outer join is indeed necessary, so full marks for anticipating that potential situation, sketch

    finally, let's take another look at the stated requirement that the name be equal to 'kenny'

    if only the names are selected, and all the names must be 'kenny', then does it really matter how you sort them??!!

    therefore, the join to the countries table is not necessary

    more importantly, the sort is not necessary, so all you guys that had an ORDER BY have got an inefficient solution

    apparently somebody once asked albert einstein, if he had only one hour to save the world, what would he do, and his answer was, he would spend the first fifty minutes analyzing the problem

    so here's the query that delivers what was asked:
    Code:
    select 'kenny' 
      from names
     where name = 'kenny'
    one of the most important rules that you must learn when writing sql is to understand your requirements

    now, to be totally fair, i'm pretty sure The Mog did not intend to pose a question which has such a trivial -- and useless -- solution

    but there are all kinds of queries that The Mog might have had in mind, so how many of these do we answer?

    yes, when we think a question is not useful, we should come back to the user with other potential solutions, but amongst them, one of them has to be the solution to the original question as asked

    sorry to be such a pedant about this, but it's an important point

    study your requirements carefully before attempting to solve the problem


    rudy

  6. #6
    midnight coder
    Join Date
    Dec 2000
    Location
    The flat edge of the world
    Posts
    838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    whoops
    Last edited by Robo; Feb 21, 2003 at 23:24.
    Work smarter, not harder. -Scrooge McDuck

  7. #7
    Happy Holidays !! Paul S's Avatar
    Join Date
    Mar 2001
    Location
    Mexico
    Posts
    1,287
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here's mine
    Code:
    SELECT country, name FROM names, countries WHERE names.countryid=countries.id AND names.name='kenny' ORDER BY country,name
    Paul


Bookmarks

Posting Permissions

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