SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Thread: Need sql query

  1. #1
    SitePoint Member
    Join Date
    Feb 2010
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need sql query

    Hi,

    Iím very new to SQL and databases. I need a query for the following: (Iím sure google would have found the answer, but I could not really frame the sentence for the task Iím looking for. Also, please let me know how do I search in google for such tasks Ė so that I can try it myself in future).

    Assume I have a table like:

    Sl No Fruit Grown in states
    1 Apple KA
    2 Orange AP
    3 Banana TN
    4 Jackfruit MH
    5 Mango MP
    6 Jackfruit MP
    7 Banana AP
    8 Mango KA
    9 Banana TN
    10 Apple MH
    11 Jackfruit AP
    12 Orange MH
    13 Mango KA
    14 Apple TN
    15 Banana MP
    16 Banana MH
    17 Mango KA
    18 Orange MP
    19 Jackfruit AP
    20 Apple TN

    From the above table, I want a SQL query which will list me the unique fruits and the states in which they are grown, like: (comma not necessary - i can add it using perl)

    Apple: KA, MH, TN
    Banana: TN, AP, MP, MH
    Jackfruit: MH,MP,AP
    Mango: MP, KA
    Orange: AP,MH,MP

    Thanks in advance for the help,
    Ravi.

  2. #2
    SitePoint Addict SirAdrian's Avatar
    Join Date
    Jul 2005
    Location
    Kelowna, BC
    Posts
    289
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try something like this

    Code:
    SELECT fruit
         , GROUP_CONCAT(state) AS states
      FROM yourtable
    GROUP
        BY fruit
    ORDER
        BY fruit;
    Adrian Schneider - Web Developer

  3. #3
    SitePoint Member
    Join Date
    Feb 2010
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi SirAdrian,

    Thanks a lot for the reply. It works!!.


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
  •