INNER JOINS - More than one?

[chromis]

Hi there,

I have three tables:

-properties
-propLocations
-locations

I am trying to allow each property to have more than one location. Locations holds the locations (id,name) and propLocations holds the references for each property so each row in this table has the ID of the property (propID) and the ID of the location propLocID.

I've been trying to get a results query back which has all of the properties columns and a column on the end containing a comma delimited list of the locations for that property.

This is as far as I have got:

SELECT
properties.id,
properties.name ,
properties.tenu re,
properties.size ,
properties.size Acres,
properties.pric e,
properties.rent al,
properties.onAp plication,
properties.team Member,
properties.pdf,
properties.imag e,
properties.date ,
properties.arch ive,
locations.locat ion

FROM properties
LEFT OUTER JOIN locations ON locations.id = propLocations.p ropLocID INNER JOIN propLocations ON propLocations.p ropID = properties.id;

So that returns the property columns and the locations.locat ion (name) for the property, this is ok for one location but what if there are more? How do I get a comma delimited list of the locations?

Hope you can help,

Chromis

[mwasif]

Hi chromis,

Welcome to TSDN!!!

This is a better approach to write the JOINS.

SELECT * FROM properties 
INNER JOIN propLocations ON properties.id= propLocations.propID
INNER JOIN locations ON propLocations.propLocID = locations.id;

I made this query without knowing your column names.

You can change the above query to get locations as well with the help of GROUP_CONCAT()

SELECT *, GROUP_CONCAT(locations.location) FROM properties 
INNER JOIN propLocations ON properties.id= propLocations.propID
INNER JOIN locations ON propLocations.propLocID = locations.id
GROUP BY properties.id;

Post back if you face problems. But please post your table structure too.

[chromis]

Hi mwasif,

Thanks very much for your speedy reply and the warm welcome. I have attempted to use the query you gave me but ran into problems with the GROUP_CONCAT() function.

I ran the following statement:

SELECT *, GROUP_CONCAT(locations.location) FROM properties
INNER JOIN propLocations ON properties.id= propLocations.propID
INNER JOIN locations ON propLocations.propLocID = locations.id
GROUP BY properties.id;
 

and it returned this error:

#1064 - You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near '( locations.location )
FROM properties
LEFT  OUTER  JOIN locati
 

I've checked the syntax and it all seems ok. Would GROUP_CONCAT() throw an error if there was only one location returned for each property?

Table Structures
----------------------------------------------------------

#
# Table structure for table `properties`
#
 
CREATE TABLE properties (
  id int(11) NOT NULL auto_increment,
  name text NOT NULL,
  number text NOT NULL,
  street text NOT NULL,
  town text NOT NULL,
  postcode text NOT NULL,
  description longtext NOT NULL,
  type text NOT NULL,
  tenure text NOT NULL,
  size int(11) NOT NULL default '0',
  sizeAcres int(11) NOT NULL default '0',
  location varchar(255) NOT NULL default '',
  price int(11) NOT NULL default '0',
  rental int(11) NOT NULL default '0',
  onApplication char(3) NOT NULL default 'no',
  teamMember varchar(255) NOT NULL default '',
  pdf text NOT NULL,
  image text NOT NULL,
  date date NOT NULL default '0000-00-00',
  archive int(11) NOT NULL default '0',
  PRIMARY KEY  (id)
) TYPE=MyISAM COMMENT='Properties';
 
#
# Table structure for table `locations`
#
 
CREATE TABLE locations (
  id int(11) NOT NULL auto_increment,
  pseudoname varchar(255) NOT NULL default '',
  location varchar(255) NOT NULL default '',
  PRIMARY KEY  (id)
) TYPE=MyISAM COMMENT='City locations for property - used for search by city';
 
CREATE TABLE proplocations (
  propID int(11) default NULL,
  propLocID int(11) default NULL
) TYPE=MyISAM COMMENT='Stores locations for properties';
 

[mwasif]

GROUP_CONCAT() was added in MySQL 4.1. Which MySQL version you are using? There is no problem with the syntax (I have executed the query with your DB structure).

[chromis]

Ah here is the problem it's: 4.0.12-nt

Is there another way of achieving my goal?

Many thanks,

Chromis

[mwasif]

I don't know anyother way using only MySQL. But you can do this with the combination of MySQL and any programming language (PHP, Perl etc).

Is there any problem upgrading MySQL?

[chromis]

I don't know anyother way using only MySQL. But you can do this with the combination of MySQL and any programming language (PHP, Perl etc).

Is there any problem upgrading MySQL?

Yeah i've had a good search and haven't found anything, so I'm getting our admin to upgrade mysql, we'll see how i get on from there!

Thanks for your help,

Chromis

[mwasif]

Before upgrading, checkout the changelog and upgrade notes at MySQL site. Your applications may face Client does not support authentication protocol error after upgrading to MySQL 4.1/5.0.

Good luck and happy upgrading!