SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Guru TacMaf's Avatar
    Join Date
    Dec 2005
    Location
    Manchester, United Kingdom
    Posts
    663
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Selecting with JOINs

    Hi,

    I'm not great with joins but I'm trying to select each individual entry from one table and all linked images from another like so:
    PHP Code:
    $q "SELECT 
                c.*,
                ci.imageID,
                i.imageFile
            FROM 
                "
    .DB_PREFIX."cars c 
                
                LEFT JOIN 
                    "
    .DB_PREFIX."carimages ci 
                ON 
                    ci.carID = c.carID    
                    
                LEFT JOIN 
                    "
    .DB_PREFIX."images i 
                ON 
                    i.imageID = ci.imageID
                
            WHERE 1"

    Problem is, each car is being selected multiple times. If there are 3 images, the car is shown 3 times.

    Where am I going wrong? I've tried INNER JOIN but had no luck.

    Cheers,
    Rhys

  2. #2
    SitePoint Guru TacMaf's Avatar
    Join Date
    Dec 2005
    Location
    Manchester, United Kingdom
    Posts
    663
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah, got it with GROUP BY ci.carID

  3. #3
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by TacMaf View Post
    Problem is, each car is being selected multiple times. If there are 3 images, the car is shown 3 times.

    Where am I going wrong?
    You are not going wrong. There are three images, you get three rows. Of course, the data from the cars table is duplicated in each row. You'll have to handle that in your PHP script.

    Quote Originally Posted by TacMaf View Post
    Ah, got it with GROUP BY ci.carID
    Really? But now you only get one image, don't you?

  4. #4
    SitePoint Guru TacMaf's Avatar
    Join Date
    Dec 2005
    Location
    Manchester, United Kingdom
    Posts
    663
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeh. I wanted just one image. It's for a jquery slider on the homepage showing new arrivals, so any image just to link to the page. Sorry I've just read my first post and realised I got it the wrong way round. All cars, one image for each.

    Cheers,
    Rhys


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
  •