help with SubQuery

They shouldn't be joined by advertinfo.advertinfoid = advertimages.advertinfoid?

SELECT Advertinfo.advertinfoid, MIN(Advertimages.imageid)
FROM Advertinfo 
INNER JOIN Advertimages ON advertinfo.advertinfoid = advertimages.advertinfoid
GROUP BY Advertinfo.advertinfoid

Select allOpen in new window

Something like this?

select advertinfo.AdvertType, advertinfo.Title, advertinfo.Price, advertimages.ImageUrl
from AdvertInfo 
  inner join AdvertImages N advertinfo.advertinfoid = advertimages.imageid
  inner join (select AdvertInfoid, MIN(imageid) ImageID from advertImages GROUP BY AdvertInfoid) FirstImage
        ON AdvertImages.AdvertInfoid = FirstImage.AdvertInfoid AND AdvertImages.ImageID = FirstImage.ImageID 

Select allOpen in new window

Great help and quick thanks gents and  Vitor Montalvão was correct the join is on advertinfo.advertinfoid = advertimages.advertinfoid, sorry my apologies.

Sorry Gents, just realised this isn't quite correct both your solutions return multiple copies of the same record one for each image added, I've left of the imageurl for clarity


i.e.
advertinfo.id AdvertType Title Price
1      Standard      test air gun      56.78      
26      Standard      image test      40.00      
27      Standard      image test      0.00
2      Premium      gun      34567.99      
28      Standard            0.00      
2      Premium      gun      34567.99      
2      Premium      gun      34567.99

Anyway, my query can't return duplicate records since is grouped by ID so can only return a single ID.

If you post you updated query, that would help.

I have attached a spreadsheet containing the my test data from the two tables, the first table is the advertimages table and has the fields imgid, advertinfoid and thumb, the second table is the advertinfo table and has the fields id, adverttype, title, description, price and dateadded (this is a subset of all the fields) BUT these are the fields I want in the query,  additionally  I would like to sort on dateadded.

The two tables are linked by advertimages.advertinfoid = advertinfo.id

Instead of seeing this data:
advertinfo.id AdvertType Title Price thumb
1      Standard      test air gun      56.78      a
26      Standard      image test      40.00      b
27      Standard      image test      0.00 c
2      Premium      gun      34567.99      d
28      Standard            0.00      e
2      Premium      gun      34567.99       f
2      Premium      gun      34567.99 g

What I'm after in the query is one record for each entry in the Advertinfo table with the first image  from the possibly multiple images in the advertimages table

i.e.
advertinfo.id AdvertType Title Price Thumb
1      Standard      test air gun      56.78      a
26      Standard      image test      40.00      b
27      Standard      image test      0.00 c
2      Premium      gun      34567.99      d
28      Standard            0.00     e

I hope that explains the problem

Mark
Book1.xls

Thats it thank you, all working as anticipated