hi all. im having a problem and wondering if anyone an help. i have the
following tables along with column names (too many columns so im just
including the relevent ones):

folders
---------
name
id
categoryid

photos
----------
name
folderid
id

categories
------------
name
id

im trying to get a resultset back of every folder along with its
category name and 1 example photoid from each. so far i have this

select f.*, c.name as category, p.id as preview from folders f,
categories c, photos p where c.id = f.categoryid and f.id = p.folderid

this doesnt work as most folders will have many photos in it so i get
multiple rows back for the same folder id. is there a way i can do this
so i only get 1 row per folder?

thanks

ungahz@gmail.com wrote:

> hi all. im having a problem and wondering if anyone an help. i have the
> following tables along with column names (too many columns so im just
> including the relevent ones):
>
> folders
> ---------
> name
> id
> categoryid
>
> photos
> ----------
> name
> folderid
> id
>
> categories
> ------------
> name
> id
>
> im trying to get a resultset back of every folder along with its
> category name and 1 example photoid from each. so far i have this
>
> select f.*, c.name as category, p.id as preview from folders f,
> categories c, photos p where c.id = f.categoryid and f.id = p.folderid
>
> this doesnt work as most folders will have many photos in it so i get
> multiple rows back for the same folder id. is there a way i can do this
> so i only get 1 row per folder?

I'm just translating your description, which leads me to this:

SELECT f.*,
( SELECT c.name
FROM categories
WHERE c.id = f.categoryid ) AS category,
( SELECT p.id
FROM photos
WHERE p.folderid = f.id
FETCH FIRST 1 ROW ONLY ) AS preview
FROM folders AS f

The important part is to add the FETCH FIRST clause for the photos.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

Knut Stolze wrote:
> ungahz@gmail.com wrote:
>
> > hi all. im having a problem and wondering if anyone an help. i have the
> > following tables along with column names (too many columns so im just
> > including the relevent ones):
> >
> > folders
> > ---------
> > name
> > id
> > categoryid
> >
> > photos
> > ----------
> > name
> > folderid
> > id
> >
> > categories
> > ------------
> > name
> > id
> >
> > im trying to get a resultset back of every folder along with its
> > category name and 1 example photoid from each. so far i have this
> >
> > select f.*, c.name as category, p.id as preview from folders f,
> > categories c, photos p where c.id = f.categoryid and f.id = p.folderid
> >
> > this doesnt work as most folders will have many photos in it so i get
> > multiple rows back for the same folder id. is there a way i can do this
> > so i only get 1 row per folder?
>
> I'm just translating your description, which leads me to this:
>
> SELECT f.*,
> ( SELECT c.name
> FROM categories
> WHERE c.id = f.categoryid ) AS category,
> ( SELECT p.id
> FROM photos
> WHERE p.folderid = f.id
> FETCH FIRST 1 ROW ONLY ) AS preview
> FROM folders AS f
>
> The important part is to add the FETCH FIRST clause for the photos.
>
> --
> Knut Stolze
> DB2 Information Integration Development
> IBM Germany

thanks Knut. as it turns out i forgot to include in my original post
that im pulling multiple values from the photos table. see my 2nd post
for details.(i actually double posted on accident but the 2nd one is
the correct query).


with that being said i dont think the photos subselect will work for me
in this case. any other possibilities?


thanks

tilted1 wrote:

> with that being said i dont think the photos subselect will work for me
> in this case. any other possibilities?

The subselect is scalar. Thus, if you want to return more information and
can combine it into a single, scalar value, then you can still use the
subselect.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

This is not tested. Just an idea.
WITH
photos_rn AS (
SELECT folderid
, id
, ROWNUMBER() OVER(PARTITION BY folderid ORDER BY id) rn
FROM photos
)
,Recurse
(rn, fname, fid, categoryid, category, preview) AS (
SELECT 0
, F.name, F.id
, F.categoryid
, C.name
, CAST('' AS VARCHAR(50))
FROM folders F
, categories C
WHERE C.id = F.categoryid
UNION ALL
SELECT pre.rn + 1
, pre.fname
, pre.fid
, pre.categoryid
, pre.category
, pre.preview || ', ' || new.id
FROM Recurse AS pre
, photos_rn AS new
WHERE pre.rn < 100
AND new.folderid = pre.fid
AND new.rn = pre.rn + 1
)
SELECT fname, fid, categoryid, category, preview
FROM Recurse R
WHERE rn = (SELECT MAX(rn)
FROM Recurse RM
WHERE RM.fid = R.fid
)
;