February 3, 2008 at 12:46 pm
I have three tables:
Categories (PK:categoryID, catName) - Pottery, paintings, photos
Items (PK:itemID, categoryID, itemName) - jug, "A peacock", "winter lanscape"
Images (PK:imageID, itemID, imagedescription) - 41, 42, 43 etc
I want to select one image from every category, so I am searching for 1 random item for each category and then using that itemID to select one imageID.
select m.itemID,m.imageID,uc.catName
from Images m
join Items t ON m.itemID = t.itemID
join Categories uc ON t.categoryID = uc.categoryID
and m.imageID IN
(select top 1 im1.imageID
from Images im1
where im1.itemID IN
(select top 1 t2.itemID
from Items t2
join Categories uc1 ON t2.categoryID = uc1.categoryID
where uc.categoryID = uc1.categoryID
ORDER BY NEWID()
)
)
this is not working: the number of categories returned varies and sometimes it returns more than one item per category and hence more than one image per category.
any help is appreciated,
Andrew
February 3, 2008 at 5:30 pm
If I remove ORDER BY NEWID() it works however the downside of this is I always get the same results somehow I'd like to return random imageID's..........
Andrew
February 4, 2008 at 6:11 pm
-- try this if you do not have a large number of rows
create table Display (itemID int, imageID int, catName varchar(10))
create unique index ix_tmp on Display (catName) with ignore_dup_key
------
truncate table Display
insert into Display
select -- top 10 -- depending on how many rows you have you may be able to put a TOP N clause here
m.itemID, m.imageID, uc.catName
from Images m
join Items t ON m.itemID = t.itemID
join Categories uc ON t.categoryID = uc.categoryID
order by newid()
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply