CUTTHEMUSIC
asked on
Refine stored procedure by splitting tables
Below is part of a stored procedure that I am working on
SELECT photos.recID, photos.photoFilename, photos.photoEventDate, photos.photoDate FROM photos
INNER JOIN (
SELECT photoEventDate,
(SELECT TOP 1 i.photoFilename FROM photos i WHERE i.photoEventDate = o.photoEventDate ORDER BY newid())
AS photoFilename FROM photos o GROUP BY photoEventDate)
X ON Photos.photoEventDate = X.photoEventDate and Photos.photoFilename = X.photoFilename
WHERE photos.photoDate IS NULL OR photos.photoDate BETWEEN @StartDate AND @EndDate
ORDER BY photos.photoDate DESC
Here is a sample of the table photos
recID photFilename photoEventDate photoDate
1000 10 Marshall Tucker Band 5/6/2002
1001 18 Marshall Tucker Band 5/6/2002
1002 19 Marshall Tucker Band 5/6/2002
1003 26 Hanging out at CG 1/1/2001
1004 41 Random 1/1/2001
1005 42 Random 1/1/2001
1006 49 Random 1/1/2001
1007 59 Random 1/1/2001
1008 74 Random 1/1/2001
1009 76 Random 1/1/2001
1010 158 H and S Party 6/9/2002
1011 179 H and S Party 6/9/2002
1012 181 Random 1/1/2001
1013 191 On the boardwalk 6/17/2002
What I am finding now is that I want to store all the photoEventDate information in a different table (maybe call it photoEventNames) and join the two using a new column in photos called photoEventID and then using the primary id from photoEventNames
I can figure out how to do everything up to this point but I'm not sure how to make the proper changes to the SELECT statement at the beginning of the question.
SELECT photos.recID, photos.photoFilename, photos.photoEventDate, photos.photoDate FROM photos
INNER JOIN (
SELECT photoEventDate,
(SELECT TOP 1 i.photoFilename FROM photos i WHERE i.photoEventDate = o.photoEventDate ORDER BY newid())
AS photoFilename FROM photos o GROUP BY photoEventDate)
X ON Photos.photoEventDate = X.photoEventDate and Photos.photoFilename = X.photoFilename
WHERE photos.photoDate IS NULL OR photos.photoDate BETWEEN @StartDate AND @EndDate
ORDER BY photos.photoDate DESC
Here is a sample of the table photos
recID photFilename photoEventDate photoDate
1000 10 Marshall Tucker Band 5/6/2002
1001 18 Marshall Tucker Band 5/6/2002
1002 19 Marshall Tucker Band 5/6/2002
1003 26 Hanging out at CG 1/1/2001
1004 41 Random 1/1/2001
1005 42 Random 1/1/2001
1006 49 Random 1/1/2001
1007 59 Random 1/1/2001
1008 74 Random 1/1/2001
1009 76 Random 1/1/2001
1010 158 H and S Party 6/9/2002
1011 179 H and S Party 6/9/2002
1012 181 Random 1/1/2001
1013 191 On the boardwalk 6/17/2002
What I am finding now is that I want to store all the photoEventDate information in a different table (maybe call it photoEventNames) and join the two using a new column in photos called photoEventID and then using the primary id from photoEventNames
I can figure out how to do everything up to this point but I'm not sure how to make the proper changes to the SELECT statement at the beginning of the question.
Ignore my sql. That is not correct!!
Which table PhotoFileName is now stored? Photos or PhotosEventNames table?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
When I run your solution no records are returned.
Here is my tabel setup
TABLE PhotoEventNames
recID
photoEventDate
TABLE photos
recID
photoFilename
photoDate
photoEvent
the join should be performed on PhotoEventNames.recID and photos.photoEvent
Here is my tabel setup
TABLE PhotoEventNames
recID
photoEventDate
TABLE photos
recID
photoFilename
photoDate
photoEvent
the join should be performed on PhotoEventNames.recID and photos.photoEvent
ASKER
I have requested that this question be deleted.
https://www.experts-exchange.com/questions/20802085/NOV-21-Please-delete.html
https://www.experts-exchange.com/questions/20802085/NOV-21-Please-delete.html
I think this question can be awarded the points as author stated that these tables are to be joined by PhotoEventId
>What I am finding now is that I want to store all the photoEventDate information in a >different table (maybe call it photoEventNames) and join the two using a new column in >photos called photoEventID and then using the primary id from photoEventNames
In a scenario like this one can only help and give an idea as to what can be done to solve the issue and not the exact answer.
If author decided against giving points that is okay for me. But one has to think that everyone's time is valuable.
>What I am finding now is that I want to store all the photoEventDate information in a >different table (maybe call it photoEventNames) and join the two using a new column in >photos called photoEventID and then using the primary id from photoEventNames
In a scenario like this one can only help and give an idea as to what can be done to solve the issue and not the exact answer.
If author decided against giving points that is okay for me. But one has to think that everyone's time is valuable.
ASKER
namasi_navaretnam
, I agree with 100%
Thanks for your help.
, I agree with 100%
Thanks for your help.
Thanks for the understanding. Glad to help in the future.
SELECT photos.recID,
photos.photoFilename,
photos.photoEventDate,
photos.photoDate
FROM photos INNER JOIN (
SELECT photoEventDate,
(SELECT TOP 1 i.photoFilename
FROM photos i
WHERE i.PhotoEventId = o.PhotoEventId
ORDER BY newid()) AS photoFilename
FROM PhotoEventNames o GROUP BY o.photoEventDate) X
ON Photos.photoEventDate = X.photoEventDate and
Photos.photoFilename = X.photoFilename
WHERE photos.photoDate IS NULL OR photos.photoDate BETWEEN @StartDate AND @EndDate
ORDER BY photos.photoDate DESC