SEO

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@blue-fly.co.uk | http://www:blue-fly.co.uk

>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.

What exactly is your question?


Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

On Wed, May 09, 2007 at 07:14:38PM +0200, Martijn Tonies wrote:
> >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.
>
> What exactly is your question?

I think he somehow wants to return each property once only but still
have every image returned in the result.

No I want all the properties only one regardless of how many images are
attached to them. Think I need a distinct in there somewhere,
----- Original Message -----
From: "Jon Ribbens" <jon+mysql@unequivocal.co.uk>
To: <mysql@lists.mysql.com>
Sent: Wednesday, May 09, 2007 6:56 PM
Subject: Re: Query problem


> On Wed, May 09, 2007 at 07:14:38PM +0200, Martijn Tonies wrote:
>> >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.
>>
>> What exactly is your question?
>
> I think he somehow wants to return each property once only but still
> have every image returned in the result.
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=ross@aztechost.com
>
>

> how do I return a single row per property even if it has 3 or 4 images
> attached to it.

Please reply to the list instead of directly to me.

You could do a:

select p.from properties p where exists (select i.* from images i
where i.property_id = p.property_id)



> > >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.
> >
> > What exactly is your question?

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com