August 11, 2012 at 1:32 am
here i am having two table @alubm and @images fom this two table in want to write a select query
two tables
DECLARE @alubm table
(
alid int,
cattyp varchar(10),
altitle varchar(30),
crdate datetime
)
insert into @alubm
select 1,'cri','bowler',GETDATE() union all
select 2 ,'cri','batsman',GETDATE() union all
select 3 ,'cri','fielders',GETDATE()
DECLARE @images table
(
imageid int,
alid int,
imagedescription varchar(30),
filenameimage varchar(30)
)
insert into @images
select 1,1,'cri1','bowler1' union all
select 2,1,'cri2','bowler2' union all
select 3,1,'cri3','bowler3' union all
select 4,2 ,'crib1','batsman1' union all
select 5,2 ,'crib2','batsman2' union all
select 6,2 ,'crib3','batsman3' union all
select 7,3 ,'crif1','fielders1' union all
select 8,3 ,'crif2','fielders2'
the select query for this will provide a result like this
select
imageid,
alid ,
imagedescription,
filenameimage
from
@images
select
alid ,
cattyp ,
altitle,
crdate
from
@alubm
imageidalidimagedescription filenameimage
11cri1 bowler1
21cri2 bowler2
31cri3 bowler3
42crib1 batsman1
52crib2 batsman2
62crib3 batsman3
73crif1 fielders1
83crif2 fielders2
alidcattypaltitlecrdate
1cribowler2012-08-11 01:05:44.313
2cribatsman2012-08-11 01:05:44.313
3crifielders 2012-08-11 01:05:44.313
and now joining this two table an d iam passing the parameter
DECLARE @cattyp varchar(3)='cri'
select
a.alid ,
a.cattyp ,
a.altitle,
a.crdate ,
b.imageid ,
b.filenameimage
--,
--b.imagedescription
from
@alubm a
inner join
@images b
on
a.alid=b.alid
where
a.cattyp=@cattyp
which will provide output like this
alidcattypaltitle crdateimageidfilenameimage
1cribowler2012-08-11 1bowler1
1cribowler2012-08-11 2bowler2
1cribowler2012-08-11 3bowler3
2cribatsman2012-08-114batsman1
2cribatsman2012-08-11 5batsman2
2cribatsman2012-08-11 6batsman3
3crifielders 2012-08-11 7fielders1
3crifielders 2012-08-11 8fielders2
right now i am trying to get output like this selecting distinct allid and only one imageid and one file name
alidcattypaltitle crdateimageidfilenameimage
1cribowler2012-08-11 1bowler1
2cribatsman2012-08-114batsman1
3crifielders 2012-08-11 7fielders1
August 11, 2012 at 11:41 am
i just found the solution for this my friends no thanks
August 11, 2012 at 4:34 pm
sivag (8/11/2012)
i just found the solution for this my friends no thanks
Hello sivag
would you care to share the solution you found please?
This may well assist othesr in the future searching for similar problems.
many thanks
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 12, 2012 at 10:54 pm
hi J Livingston
i didn't use rand() function instead of that i used NEWID() function to select randomly this is solution for imageid and imagedescription
DECLARE @alubm table
(
alid int,
cattyp varchar(10),
altitle varchar(30),
crdate datetime
)
insert into @alubm
select 1,'cri','bowler',GETDATE() union all
select 2 ,'cri','batsman',GETDATE() union all
select 3 ,'cri','fielders',GETDATE()
DECLARE @images table
(
imageid int,
alid int,
imagedescription varchar(30),
filenameimage varchar(30)
)
insert into @images
select 1,1,'cris1','bowler1' union all
select 2,1,'cris2','bowler2' union all
select 3,1,'cris3','bowler3' union all
select 4,2 ,'crib1','batsman1' union all
select 5,2 ,'crib2','batsman2' union all
select 6,2 ,'crib3','batsman3' union all
select 7,3 ,'crif1','fielders1' union all
select 8,3 ,'crif2','fielders2'
declare @cattyp varchar(3)='cri'
;WITH RandomValues
AS(
SELECT distinct top 9 a.alid ,
a.cattyp ,
a.altitle,
a.crdate
,(Select top 1 b.imageid
FROM @images b
where
b.alid=a.alid order by NEWID() ) ImageId
FROM
@alubm a
inner join
@images b
on
a.alid=b.alid
where
a.cattyp=@cattyp
group by
a.alid ,
a.cattyp ,
a.altitle,
a.crdate
ORDER BY
a.crdate DESC
),
RandomNodes
AS(
SELECT
alid ,
cattyp ,
altitle,
crdate ,
ImageId
FROM
RandomValues
),
RandomFileDescription
AS(
SELECT
s.alid ,
s.cattyp ,
s.altitle,
s.crdate ,
s.ImageId,
(Select top 1 b.imagedescription from @images b where
b.alid=s.alid
and b.ImageId=s.ImageId ) Description
FROM
RandomNodes s
inner join
@images i
on
s.ImageId=i.ImageId
)
SELECT
alid ,
cattyp ,
altitle,
cast(crdate as date) crdate,
ImageId,
Description
FROM
RandomFileDescription
ORDER BY
crdate DESC
;
August 13, 2012 at 3:13 am
possibly another way....assuming you want a random image returned.
does this return what you want?
declare @cattyp varchar(3)='cri'
;with cte as
(
SELECT alid ,
imageid ,
filenameimage ,
ROW_NUMBER( )OVER( PARTITION BY alid ORDER BY NEWID( )DESC )AS rn
FROM @images
)
SELECT a.alid ,
a.cattyp ,
a.altitle ,
a.crdate ,
CTE.imageid ,
CTE.filenameimage
FROM
@alubm AS a INNER JOIN CTE ON a.alid = CTE.alid
WHERE CTE.rn = 1
AND a.cattyp = @cattyp;
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 13, 2012 at 3:32 am
Here's another way:
declare @cattyp varchar(3) = 'cri'
SELECT a.alid,
a.cattyp,
a.altitle,
a.crdate,
x.imageid,
x.filenameimage
FROM @alubm AS a
CROSS APPLY (
SELECT TOP 1 i.imageid, i.filenameimage
FROM @images i
WHERE i.alid = a.alid
ORDER BY NEWID()
) x
WHERE a.cattyp = @cattyp;
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply