Cant get my table joins working correctly

[spiderwell]

hi all, having a problem with my multi table sql statement. I have 4 tables, vehiclesales,vehiclemake,vehiclemodel and vehicleimages

I am trying to return rows from vehiclesales and join vehicle make and model and images onto it. now the make and model work fine as its a straight forward 1 to 1 but vehicle images can be many rows per vehiclesale, and I only want it to return 1 image, doesnt matter which one really. my statement works fine until i add in the vehicle images bit, i was using inner jons for all but i tried using left and right but it doesnt seem to make any difference, as soon as i add the vehicleimages join, instead of 4 unique vehicles i get them repeating each with a different image in the row. What join would i need to get just 1 image per vehicle without repeating the vehiclesales.

SELECT * FROM vehiclesales LEFT JOIN vehiclemake ON vehiclesales.makeid = vehiclemake.ID LEFT JOIN vehiclemodel ON vehiclesales.modelid = vehiclemodel.ID LEFT JOIN vehicleimages ON vehiclesales.ID = vehicleimages.salesid ORDER BY `vehiclesales`.`ID` DESC LIMIT 30

 

I have read some join tutorials and it seems that left join should do the trick but alas not.

 

any suggestions much appreciated

 

[fenway]

I'd suggest that you read the sticky on how to provide all of the necessary information.

[spiderwell]

i thought I had provided all the information?

 

 

[spiderwell]

ok seems i have missed out some bit, i will have to come back to this as I dont have it here with me right now

[fenway]

ok seems i have missed out some bit, i will have to come back to this as I dont have it here with me right now

No problem -- we'll wait.

[spiderwell]

ok here is the structure of the 4 tables with also teh mysql version

-- phpMyAdmin SQL Dump
-- version 3.3.9
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Feb 22, 2012 at 07:34 PM
-- Server version: 5.1.53
-- PHP Version: 5.3.4

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `hoagysouthwest`
--

-- --------------------------------------------------------

--
-- Table structure for table `vehicleimages`
--

CREATE TABLE IF NOT EXISTS `vehicleimages` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `salesid` int(11) DEFAULT NULL,
  `image` text,
  `order` int(11) DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=41 ;

-- --------------------------------------------------------

--
-- Table structure for table `vehiclemake`
--

CREATE TABLE IF NOT EXISTS `vehiclemake` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `make` text NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

-- --------------------------------------------------------

--
-- Table structure for table `vehiclemodel`
--

CREATE TABLE IF NOT EXISTS `vehiclemodel` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `makeid` int(11) DEFAULT NULL,
  `model` text NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;

-- --------------------------------------------------------

--
-- Table structure for table `vehiclesales`
--

CREATE TABLE IF NOT EXISTS `vehiclesales` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `makeid` int(11) NOT NULL,
  `modelid` int(11) NOT NULL,
  `title` text,
  `year` int(11) DEFAULT NULL,
  `date` date DEFAULT NULL,
  `price` int(11) DEFAULT NULL,
  `mot` text,
  `tax` text,
  `logbook` int(11) DEFAULT NULL,
  `mileage` int(255) DEFAULT NULL,
  `fuel` text,
  `transmission` text,
  `drive` int(11) DEFAULT NULL,
  `details` text,
  `forsale` int(11) NOT NULL DEFAULT '0',
  `ebay` bigint(200) DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;

 

What I am trying to achieve is a recordset of the vehiclesales, with vehiclemake, vehiclemodel and vehicleimages all joined on to give me a list of all the vehicle sales with just 1 row from the images. as it stands vehiclemodel and vehiclemake are 1 to 1 against vehiclesales, but vehiclesales to vehicleimages can be 1 to many, I just want it to return 1 image.

 

I hope this is enough information for you

[fenway]

Getting closer -- so what output does that produce, and what's wrong about it?

[spiderwell]

i have 4 vehiclesales entries, and vehicleimages has like 4 images with the same vehicleID, so when i run the statement i get like 16rows back which repeat the vehiclesales each time with a different image in the row, so the same vechiclesale appears 4times when i just want 1

so im trying to pull out each vehiclesale with an image but i dont want the sql to repeat the vehiclesales with each image that has a matching vehicleid

 

i hope that makes sense

[fenway]

Sorry, lost track of this thread for a while -- any progress/updates?

[kickstart]

Hi

 

You are using LEFT JOINs (although INNER JOINs would have much the same issue here).

 

Yo are getting back one row per related image. If you want just a single row you need to either concatenate the differing columns together (ie, maybe use GROUP_CONCAT) or specify which of the JOINed rows you want to bring back (ie, in this case which image).

 

A 3rd option, which is pretty messy, would be to join the table against the images, once for each possible image. This is a pretty nasty way to do it (and would need to cope with the max number of images, so coping with 100 possible images when 99% of the time there were only 4 would be very messy and inefficient).

 

Normally if you want all to display all the image rows you would bring back one row per image and sort out the display in PHP.

 

All the best

 

Keith