complicated query - help would be much appeciated!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

complicated query - help would be much appeciated!

Author

Topic

ts_abbott@hotmail.com
Starting Member

36 Posts

Posted - 2005-01-19 : 11:19:03

I've got a complicated query I need to build for an xml feed but i can't
figure out how.

Here it is:

1) I've got x number of products (tbl. Products)
2) Each product may have any number of images associated to it in the Images
table (Images.ProductId->Products.ProductId)
3) I need to return lets say 150 products BUT each product must only pick 1
associated image and that image must be random every time.

I've got access to a dbo.RandNum() function which generates a random number
for every row if that helps.

i.e.

SELECT PrizeId, ImageThumbnail, ImageLarge, dbo.Random() FROM Images:

produces:

1, x.jpg, x_l.jpg, 0.1232432344
1, x2.jpg, x2_l.jpg, 0.343575343
2, y.jpg, y_l.jpg, 0.3224372432

The images table has ImageId, ImageThumbnail, ImageLarge fields.

Any help would be much appreciated.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-01-19 : 11:52:27

Join to a derived table...

select top 1 ...
from Images
order by newid())

Jay White

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-19 : 11:52:31

how bout:
select *
from Products t1 inner join
(SELECT PrizeId, ImageThumbnail, ImageLarge, max(dbo.Random())
FROM Images
group by PrizeId, ImageThumbnail, ImageLarge) t2 on t1.ProductId = t2.ProductId

Go with the flow & have fun! Else fight the flow

ts_abbott@hotmail.com
Starting Member

36 Posts

Posted - 2005-01-19 : 11:56:52

quote:
Originally posted by spirit1

how bout:
select *
from Products t1 inner join
(SELECT PrizeId, ImageThumbnail, ImageLarge, max(dbo.Random())
FROM Images
group by PrizeId, ImageThumbnail, ImageLarge) t2 on t1.ProductId = t2.ProductId

Go with the flow & have fun! Else fight the flow

can't group by PrizeId, ImageThumbnail, ImageLarge because ImageThumbnail & ImageLarge will be different for each image.

ts_abbott@hotmail.com
Starting Member

36 Posts

Posted - 2005-01-19 : 12:05:27

quote:
Originally posted by Page47

Join to a derived table...

select top 1 ...
from Images
order by newid())

Jay White

hi, could you explain this in more details pls - and if possible some sample sql.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-01-19 : 13:04:15

[code]
create table #Products(
ProductID int not null primary key)
create table #Images(
Img varchar(20) not null,
ProductID int not null,
constraint pk_images primary key (Img, ProductID))

insert into #Products
select 1 union select 2 union select 3

insert into #Images
select 'a', 1 union select 'b',1 union select 'c',1 union
select 'a', 2 union select 'b',2 union select 'c',2 union
select 'a', 3 union select 'b',3 union select 'c',3

select
p.ProductID,
(select top 1
Img
from
#Images
where
ProductID = p.ProductID
order by
newid()) as 'Image'
from
#Products p
[/code]

Jay White

ts_abbott@hotmail.com
Starting Member

36 Posts

Posted - 2005-01-19 : 13:32:09

quote:

Originally posted by Page47


create table #Products(
    ProductID int not null primary key)
create table #Images(
    Img varchar(20) not null,
    ProductID int not null,
    constraint pk_images primary key (Img, ProductID))

insert into #Products
select 1 union select 2 union select 3

insert into #Images
select 'a', 1 union select 'b',1 union select 'c',1 union
select 'a', 2 union select 'b',2 union select 'c',2 union
select 'a', 3 union select 'b',3 union select 'c',3

select
    p.ProductID,
   (select top 1
        Img
    from
        #Images
    where
        ProductID = p.ProductID
    order by
        newid()) as 'Image'
from
    #Products p

Jay White

Hi Jay, thanks for the reply. This is exactly what I want to do but I need to get 2 columns from the Image table - is this possible.

Thanks very much!

ts_abbott@hotmail.com
Starting Member

36 Posts

Posted - 2005-01-19 : 13:55:42

Hi,

I've solved the problem:


SELECT a.PrizeId, i.[Name], i.Thumbnail
FROM
	(SELECT p.PrizeId,
		(SELECT TOP 1 [UIID]
		 FROM Images
		 WHERE PrizeId=p.PrizeId
		 ORDER BY newid()) AS UIID
	FROM Products p) a
INNER JOIN Images i ON a.UIID=i.UIID

Thanks for all your replies

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources