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.
LVL 2
CUTTHEMUSICAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

namasi_navaretnamCommented:
Try This,

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
0
namasi_navaretnamCommented:
Ignore my sql. That is not correct!!
0
namasi_navaretnamCommented:
Which table PhotoFileName is now stored? Photos or PhotosEventNames table?
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

namasi_navaretnamCommented:
OK Try this,

SELECT photos.recID,
       photos.photoFilename,
       pen.EventDate,
       photos.photoDate
FROM   photos  INNER JOIN (
            SELECT o.photoEventId,
                  (SELECT TOP 1 i.photoFilename
                     FROM photos i
                    WHERE i.PhotoEventId = o.PhotoEventId
                    ORDER BY newid()) As photoFilename
              FROM PhotoEventNames o GROUP BY o.photoEventId) X
       ON Photos.photoFilename = X.photoFilename and
          Photos.photoEventId = X.photoEventId
       INNER JOIN PhotoEventNames pen ON
           Photos.photoEventId = pen.photoEventId    
WHERE photos.photoDate IS NULL OR photos.photoDate BETWEEN @StartDate AND @EndDate
ORDER BY photos.photoDate DESC


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CUTTHEMUSICAuthor Commented:
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
0
CUTTHEMUSICAuthor Commented:
I have requested that this question be deleted.
http://www.experts-exchange.com/Community_Support/Q_20802085.html
0
namasi_navaretnamCommented:
OK.
0
namasi_navaretnamCommented:
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.
0
CUTTHEMUSICAuthor Commented:
namasi_navaretnam
, I agree with 100%
Thanks for your help.
0
namasi_navaretnamCommented:
Thanks for the understanding. Glad to help in the future.

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.