GROUP BY with RIGHT JOIN problem

[Paulkirkewalker]

Hi there,

I have two tables: properties and images.  They are linked by the primary key of properties, property_id.  Some records in properties have more than one record in images associated with them  and some have none at all.
What I am trying to do is return all the records in properties, whether they have an associated record in images or not [i]but[/i] only return one record (it doesn't matter which) for those which have more than one record in images associated with them.

If I run:
[code]
SELECT images.*
FROM images
GROUP BY images.property_id
[/code]
I get just one record associated with each property.  Fine.  My query linking properties and images runs fine too:
[code]SELECT properties.*, images.*
FROM images RIGHT JOIN properties ON images.property_id = properties.property_id;
[/code]

As expected, it returns every record in properties along with associted records in images whether any exist or not.  The problem comes when I try to add the GROUP BY line to this query:
[code]
SELECT properties.*, images.*
FROM images RIGHT JOIN properties ON images.property_id = properties.property_id;
GROUP BY images.property_id
[/code]

I just get 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 'GROUP BY images.property_id' at line 1

Can anyone help?  This is driving me mad!  I accept that my approach with these queries may be completely wrong to start with so I'd be very grateful for any advice.

Thanks,
Paul.


But when I add the same GROUP BY line to the end on

[printf]

the semicolon should be after the GROUP BY clause. You have it at the end of FROM line.

printf

[fenway]

Also, don't use right join -- flip it around as use LEFT JOIN.