Refine stored procedure by splitting tables

CUTTHEMUSIC
CUTTHEMUSIC used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
Ignore my sql. That is not correct!!
Which table PhotoFileName is now stored? Photos or PhotosEventNames table?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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


Author

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

Author

Commented:
I have requested that this question be deleted.
http://www.experts-exchange.com/Community_Support/Q_20802085.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.

Author

Commented:
namasi_navaretnam
, I agree with 100%
Thanks for your help.
Thanks for the understanding. Glad to help in the future.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial