Barry wrote:
Can someone plaese help? I have three tables 1st is a collection of
propertys, 2nd is a list of facilities(98 in total) and the third is a
list of property id's corresponding to the facilities id's offered at
each property,
The query I am running:
'SELECT'
'`'.$type.'`.`id`,'
'`'.$type.'`.`name`,'
'`'.$type.'`.`addr_1`,'
'`'.$type.'`.`addr_2`,'
'`'.$type.'`.`addr_3`,'
'`'.$type.'`.`addr_4`,'
'`'.$type.'`.`post_code`,'
'`'.$type.'`.`short_desc`,'
'`'.$type.'`.`phone_1`,'
'`'.$type.'`.`email`,'
'`'.$type.'`.`website`,'
'`'.$type.'`.`cost_single`,'
'`'.$type.'`.`cost_double`,'
'`'.$link.'`.`f_id`, `facilities`.`path`'
'FROM `'.$type.'`'
'inner JOIN `'.$link.'` ON `'.$type.'`.`id` = `'.$link.'`.`id`'
'inner JOIN `facilities` ON `'.$link.'`.`f_id` = `facilities`.`f_id`'
'where `name` like '. $name.''
works after a fashion, except that for each facility I get a
duplicate property entry in my results row, (if there are ten
facilities I get ten listing for the same property).
Which is exactly what you asked for in the query!
What I am trying to achieve is one result row for each property along
with a list of facilites offered.
You can either solve this in the application that displays the result of
the query (if the property of the row is the same as that of the
previous row, just add the facility to a temporary list and else display
the temporary list of facilities), or you can use the GROUP_CONCAT()
function along with a GROUP BY (GROUP_CONCAT() was added in MySQL 4.1).
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html
E.g. SELECT ..... , GROUP_CONCAT(`facilities`.`path`) FROM ... WHERE
.... GROUP BY `type`.`id`;
Kind regards, Jigal.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
