SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member
    Join Date
    Oct 2002
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Returning Single Rows from joined query

    Sorry if the subject is a bit garbled! Here is what i want to know is (or isn't) possible.

    Assuming I have a table of companies that offer services in different (multiple) areas, a table of areas (eg. countries), and an index table as follows:

    Companies
    - ID
    - name
    - url
    - phone number

    Areas
    - ID
    - name

    Company_Areas
    - company_id
    - area_id

    If I have a company called "My Company" who offer services in the UK, USA and Australia is it possible to have a MySQL query return the list of countries as one field so that I can return all information for each company in a single row and without multiple queries? ie. return a result set like this:

    ID = 1
    name = My Company
    url = www.mycompany.com
    phone = 123456
    areas = UK, USA, Australia

    Thanks in advance for any help!

    Simon

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    what you describe is best done in a script

    if this were sql/server, it would be a simple stored procedure with a loop

    since it's mysql, you'll have to do it in whatever language (php?) you're using to connect to mysql

    if you're using coldfusion, there's an elegant solution involving the CFOUTPUT GROUP= parameter

    there is a pure sql solution, but it involves left outer joins with as many tables as necessary to cover the maximum number of areas per company -- in other words, ugly, complex, and inefficient as heck

    rudy


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
  •