Jump to content

Cant get my table joins working correctly


spiderwell

Recommended Posts

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

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.