Hopefully simple solution LIMITing second table 1

[clivehenderson]

Sounds simple but I'm really struggling.
Any help appreciated.
Thanks
Clive

Properties have many images

[Blue]tblProperty tblPropertyImage
Prop ID ImageID, ImagePropID, ImageSeq[/blue]

I'm just trying to produce one record per property with images details from the lowest ImageSeq.

[Blue]e.g
tblProperty tblPropertyImage
PropID=1 ImageID=11, ImagePropID=1, ImageSeq=5
ImageID=17, ImagePropID=1, ImageSeq=77

PropID=2 ImageID=99, ImagePropID=2, ImageSeq=3
[/blue]
So I'd like to get just 2 results:

[Blue]PropID=1,ImageID=11, ImagePropID=1, ImageSeq=5
PropID=2,ImageID=99, ImagePropID=2, ImageSeq=3[/blue]

 

[TonyGroves]

If you're using MySQL 4.1, you could try:
[tt]
SELECT i.imagepropid,i.imageid,i.imageseq
FROM
tblpropertyimage i
JOIN
(
SELECT imagepropid,MIN(imageseq) m
FROM tblpropertyimage
GROUP BY imagepropid
) sq
ON (i.imagepropid=sq.imagepropid AND i.imageseq=sq.m)
[/tt]
tblProperty is irrelevant here.

 

[clivehenderson]

Tony
Thanks for a superfast response.
I do actually need other data from the tblProperty.
Could you please see if this can be included?
Thanks
Clive

 

[TonyGroves]

Just join tblProperty to the existing join, and select the fields you want.

 

[clivehenderson]

Thanks again Tony
Now I show my true ignorance...
I can include the tblProperty but not any fields - keep getting MYSQL syntax errors.
Could you please update your solution above which works.
Many thanks
Clive

 

[TonyGroves]

Can you show us the code you've tried, and what the syntax errors say?

I don't mean to be awkward, but much as I like helping people, I don't want to be just dishing out full source code solutions without seeing that the questioner has made some effort themselves.

 

[clivehenderson]

Hi Tony
The code is below. I've changed the field names to the actual ones on the database.
I've included the tblProperty table in the join.
The code in blue does not throw up errors but the ones in red do.
I generally do my queries in MsAccess and copy paste into Dreamweaver. This is way beyond the SQL I'm used to!
I'm testing he SQL using MYSQL query which I've recently downloaded and trying various combinations of where to put the tblProperty fields without much success.

SELECT i.ImageBelongsTo,i.ImageID,i.ImageSeq [Red]j.PropRef[/Red]
FROM
  tblPropImages i [Red]tblProperty j[/Red]
  JOIN
    (
      SELECT ImageBelongsTo,MIN(ImageSeq) m
      FROM tblPropImages [Blue]tblProperty[/Blue]
      GROUP BY ImageBelongsTo
    ) sq
    ON (i.ImageBelongsTo=sq.ImageBelongsTo AND i.ImageSeq=sq.m)

The type of error I'm getting is:
[Red]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 'tblProperty j
JOIN
(
SELECT ImageBelongsTo,MIN(ImageSeq) m
' at line 3 [/Red]

 

[TonyGroves]

This should work:
[tt]
SELECT
i.imagebelongsto,i.imageid,
i.imageseq,p.propref
FROM
tblpropertyimage i
JOIN
(
SELECT imagebelongsto,MIN(imageseq) m
FROM tblpropertyimage
GROUP BY imagebelongsto
) sq
ON (i.imagebelongsto=sq.belongsto AND i.imageseq=sq.m)
JOIN tblproperty p ON (i.imagebelongsto=p.propid)
[/tt]

 

[TonyGroves]

Sorry, "sq.belongsto" should be "sq.imagebelongsto".

 

[clivehenderson]

Tony
Thanks very much for all the time and trouble you've spent on this. All now works perfectly.
Best wishes
Clive