List:General Discussion« Previous MessageNext Message »
From:<ross Date:May 9 2007 5:03pm
Subject:Query problem
View as plain text  
I have a table of properties that is linked to a table  f images with a one property to
many images relationship. I have manged this with nested queries but want to try and do
it on one line. My current query

$query = "SELECT * FROM images, properties WHERE images.property_id =
properties.property_id";

As you can see from the query this returns a row for every image so if a property has 3
images associated with it it will be returned 3 times. 


Thanks,

CREATE TABLE `images` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `property_id` varchar(10) default NULL,
  `name` varchar(30) NOT NULL default '',
  `type` varchar(30) NOT NULL default '',
  `size` int(11) NOT NULL default '0',
  `position` int(10) unsigned NOT NULL default '0',
  `title` varchar(100) NOT NULL,
  `img_url` varchar(200) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=264 ;



CREATE TABLE `properties` (
  `property_id` varchar(20) NOT NULL,
  `postcode` varchar(20) default NULL,
  `address` varchar(200) default NULL,
  `short_desc` varchar(500) default NULL,
  `long_desc` varchar(500) default NULL,
  `latitude` double(100,20) default NULL,
  `longitude` double(100,20) default NULL,
  `rent` varchar(50) default NULL,
  `available_from` date default NULL,
  `rent_type` varchar(255) default NULL,
  `double_rooms` int(2) default NULL,
  `single_rooms` int(2) default NULL,
  `twin_rooms` int(2) default NULL,
  `additional_rooms` varchar(500) default 'on',
  `features` varchar(500) default NULL,
  `status` enum('off','on') default 'on',
  PRIMARY KEY  (`property_id`)
) 
 t: 0131 553 3935 | m:07816 996 930 | ross@stripped | http://www:blue-fly.co.uk
Attachment: [text/html]
Attachment: [text/html]
Attachment: [text/html]
Thread
Query problemross9 May
  • Re: Query problemMartijn Tonies9 May
    • Re: Query problemJon Ribbens9 May
  • Re: Query problemross9 May
  • Re: Query problemMartijn Tonies9 May