Stuck with an SQL Query- please help :(

[fluiduk]

Hi All

Stuck on a mysql query.

I will explain what it needs to do.

The query needs to select all entries from a table (portfolio_entries) where column 'type' = 'roofgardens', it needs to get a picture for each of those entries from a table called portfolio_images, where the colunm 'portfolio_id' is the same as that from the previous part of the query. It then needs to only bring back pictures where the column 'pic_type' = 'small'... to further complicate matters there are 4 small pictures for each portfolio entry.. I only need one

Any help is appreciated, because I am well lost

aaron

 

[Haircut]

select *
from portfolio_entries  pe
    ,portfolio_images   pi
where pe.portfolio_id = pi.portfolio_id
and pe.type = 'roofgardens'
and pi.pic_type = 'small'
and pi.image_id = (select min(image_id)
                   from portfolio_images pi2
                   where pi2.portfolio_id = pi.portfolio_id
                   and pi2.pic_type = 'small'
                  )

That should do it, don't know what the column name for the images in portfolio_images table is though so have just used image_id.
Basically just done a correlated subquery on portfolio_images to get the lowest image id

 

[Welshy]

tut tut Haircut, that could have been part of his homework/assignment that you just did