SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Hybrid View

  1. #1
    SitePoint Addict frankiehots's Avatar
    Join Date
    Jan 2002
    Location
    U.S.A. *Blue State*
    Posts
    289
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help with JOIN SELECT from 3 Tables

    Is there a way to do a JOIN type of SELECT query where you are pulling data from 3 tables, but all the data gets returned as one row or one array? Right now I am trying to do a SELECT from 3 tables and I am getting results like this:

    (PART NUMBER, DESCRIPTION, PRICE, DISCOUNTED_PRICE, URL, IMAGE_URL)

    TXX-101, Widget, 59.00, /index.php
    TXX-101, Widget, 59.00, /image.jpg
    TXX-101, Widget, 55.00, /index.php
    TXX-101, Widget, 55.00, /image.jpg

    When I really want to get is a single row/array like this:
    TXX-101, Widget, 59.00, 55.00, /index.php, /image.jpg

    The root of the problem is that in my primary table the id(primary key) has always one row. In the other tables the id(foreign key) has 2 rows. This problem may be a result of the database's stucture being wrong, but I am working off of a MySQL database I am allowed to change minimally if at all.

    P.S. Running MySQL 4.0.17
    Last edited by frankiehots; Feb 9, 2005 at 14:22.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    nope, not until 4.1 when you can use the GROUP_CONCAT function

    you'll have to do that with script logic on the above result set
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict frankiehots's Avatar
    Join Date
    Jan 2002
    Location
    U.S.A. *Blue State*
    Posts
    289
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I upgraded to MySQL 4.1.9 and the GROUP_CONCAT function does exactly what I need. That is ONE USEFUL function when you are joining multiple tables. I can't believe that it wasn't in previous versions. That feature alone makes the upgrade to 4.1 worth it.

    With the values being concatenated by MySQL and separated by commas, I just use the PHP explode function to make them easily accessible:

    $values = explode(',', $row['3']);


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
  •