Results 1 to 9 of 9
  1. #1
    Join Date
    May 2012
    Posts
    4

    Unanswered: Construct a query (challenging one)

    Hi all,

    nice to have a forum like this. This is my first post here. My question is as follows: I have three tables as shown by the relational schema below

    Persons(PersonId,Name)
    Languages(LanguagePerson,Language)
    Places(TablePerson,Table,Chair)


    Now, both LanguagePerson and TablePerson are foreign keys, referencing to Persons(PersonId). Language can have values like 'English','French', etc.

    The query I'd like to construct shall find all tables at which the persons sitting there have at least one person to talk to (i.e. there is at least one more person at the same table who speaks the same language).
    Anybody know how to go about this?

    Thanks in advance

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    What have tried so far? Have a look at GROUP BY and HAVING.

    The way I would work this is looking per table/places, per language any group by that have a count > 1 should be displayed.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    May 2012
    Posts
    4
    Hello, thanks for replying.

    This is my "best" attempt so far.

    select p.Table from places as p, places as s, languages as l,languages as k
    where p.Table=s.Table and s.TablePerson=l.LanguagePerson and p.TablePerson=k.LanguagePerson
    and k.language=l.language group by p.Table;


    I'm looking into HAVING right now. Thank you.

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    You do not need to have two places or languages tables in your query. Let me explain:

    If you have at the same place two or more people speaking the language then you have gotten your result. So if we select grouping by the place and language and getting the count, then any count > 1 indicates where two or more people speak the same language.

    Let's say we have a table that contains the following information
    Table Person Language
    1, Tom, English
    1, Dick, French
    1, Harry, French
    2, Mickey, English
    2, Mouse, English
    2, Tom, English
    2, Thumb, English
    2, Donald, French

    If we summarized this to count the number of persons per table per language we would get the following:

    TableName Language Count
    1,English, 1
    1,French, 2
    2,English, 4
    2, French, 1

    In the example above, table 1 has 1 person who speaks english and 2 people that speak french. In table 2, 4 people speak english and 1 person speaks french. Using the HAVING clause we can filter this resultset to only those that have a count > 1
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  5. #5
    Join Date
    May 2012
    Posts
    4
    Thanks man. Using HAVING clause, as you recommended, together with your guidance on how to think, this is what I came up with that works:

    select Table from places,languages where TablePerson=LanguagePerson group by Table,language having count(Language) > 1;

    Is this how you would have done it? I bet there are other ways, and that makes it interesting.

    Again, cheers!

  6. #6
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    That is the way I would have done it. Good job!!
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If there were additional data, like
    Table Person Language
    1, Tom, English
    1, Dick, French
    1, Harry, French
    2, Mickey, English
    2, Mouse, English
    2, Tom, English
    2, Thumb, English
    2, Donald, French
    2, Alice, French

    If we summarized this to count the number of persons per table per language we would get the following:

    TableName Language Count
    1,English, 1
    1,French, 2
    2,English, 4
    2, French, 2

    So, duplicated rows might appear in the result and it might be better to specify DISTINCT.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another idea.

    Code:
    SELECT Table
     FROM  Places
         , Languages
     WHERE TablePerson = LanguagePerson
     GROUP BY
           Table
     HAVING
           COUNT(*) > COUNT(DISTINCT Language)

  9. #9
    Join Date
    May 2012
    Posts
    4
    Nice, it also works. Thanks for sharing an alternative solution!

    - Borche

Posting Permissions

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