SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Enthusiast
    Join Date
    Apr 2008
    Posts
    48
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help needed on SQL query

    I have the following table...How do I only display the countries that speak the same language?...Please Help


    +--------------+----------+------------+
    | country | language | population |
    +--------------+----------+------------+
    | England | English | 215000000 |
    | Nepal | Nepali | 1135000000 |
    | Japan | Japanese | 21350000 |
    | S.Korea | Korean | 21150000 |
    | China | Chinese | 2147483647 |
    | India | Hindi | 2147483647 |
    | Thailand | Thai | 22253410 |
    | Australia | English | 2112253410 |
    | South Africa | English | 2147483647 |
    | Spain | Spanish | 2147483647 |
    +--------------+----------+------------+

  2. #2
    SitePoint Addict CWebguy's Avatar
    Join Date
    Mar 2009
    Posts
    247
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Select * from database where language="targetlanguage"

  3. #3
    SitePoint Enthusiast
    Join Date
    Apr 2008
    Posts
    48
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the post...Actually,I want a general query...not a specific one...that lists the countries that speak the same language!!

  4. #4
    SitePoint Addict CWebguy's Avatar
    Join Date
    Mar 2009
    Posts
    247
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can you give an example?

  5. #5
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    700
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    select t.country,
           t.language,
           t.population
      from t join
     (select language
        from t
       group by language
      having count(*) > 1) dt
     on t.language = dt.language
    Last edited by swampBoogie; Mar 11, 2009 at 12:07. Reason: Need to qualify columns

  6. #6
    SitePoint Enthusiast
    Join Date
    Apr 2008
    Posts
    48
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    lets say I have the following records...


    Country Language

    England English
    USA English
    India Hindi
    Australia English
    Spain Spanish
    Colombia Spanish
    Thailand Thai

    What I want the query to return from the bale is the country that speaks the same language....in this case...I want it to return.... England, USA, Australia, Spain and Colombia as they have atleast one language common to othe countries....I hope this is helpful

  7. #7
    SitePoint Addict CWebguy's Avatar
    Join Date
    Mar 2009
    Posts
    247
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't have a database right now to test this, but maybe something like

    select * from datebase where language !(Distinct)

    (Sorry I know this won't work, but should be a head start in the right direction.

    Take care.

  8. #8
    SitePoint Enthusiast
    Join Date
    Apr 2008
    Posts
    48
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks swampboogie....I see using two tables 't' and 'dt'...but I just have one table

  9. #9
    SitePoint Enthusiast
    Join Date
    Apr 2008
    Posts
    48
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks guys...I figured it out..

    Here is my SQL query.

    select * from table1 where language=(select language from table1 having count(population)>1);

  10. #10
    SitePoint Addict CWebguy's Avatar
    Join Date
    Mar 2009
    Posts
    247
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What you want is the opposite of a unique/distinct command

  11. #11
    SitePoint Addict CWebguy's Avatar
    Join Date
    Mar 2009
    Posts
    247
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, if that works, good luck!

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    parash, the "dt" table is a derived table

    swampboogie's solution is correct

    i would be very surprised if the query in post #9 actually works, because the subquery is missing the GROUP BY clause

    besides, the IN (subquery) construction is the same as joining to a derived table

    for more (free) information on this, have a look at the sample chapter Simply SQL: The FROM Clause from my book -- subqueries and derived tables are explained on page 6 of that article

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Enthusiast
    Join Date
    Apr 2008
    Posts
    48
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the suggestion r397...I'll have a look at it.

    Now I think that my sql is kinda incorrect

    Anyways, thanks


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
  •