[SOLVED] Sub Query

[barkster]

I have a table called ads and a second table called images that has a list of images for each as by adID  I want to do a query for each and and get the first image(imageID) from the images table.  If there insn't a image then would be null.  How can I make a query like this?  I tried a subquery but didn't work for me.

 

--Ads--

AdID

AdName

 

--Images--

ImageID

AdID

ImageName

[akitchin]

this calls for a JOIN i believe:

 

SELECT DISTINCT Ads.AdID, Ads.AdName, Images.ImageID, Images.ImageName FROM Ads INNER JOIN Images ON Ads.AdID=Images.AdID

 

give that a whirl - if syntax is off, check the manual and you should be able to correct it.

[barkster]

I've tried that and it gives me duplicate rows if there are more than one image.  I need to get all rows in the Ad table and if there is an image get the first occurrence.  See below

 

--Sql--

 

SELECT DISTINCT Ads.AdID, Ads.CatID, images.Image

FROM

Ads

Left Join images ON Ads.AdID = images.AdID

 

--results--

AdID    CatID  Image

338 40_5 null

337 10_99 null

336 30 null

335 30 null

334 20 null

333 30 TestImage.jpg

333 30 TestImage2.jpg

333 30 TestImage3.jpg

332 30 null

[spankpup]

Try this:

 

select a.adid, a.adname, min(i.imageid), min(i.imagename)

from ads a

left outer join images i on a.adid = i.adid

group by 1,2

[barkster]

Thanks, that got me in the right place!!