Greetings! I am hoping someone can help me with a MS SQL query/view problem.
I am creating a "name my new baby" website where parents can upload a picture of their child and have
users cast votes for a good name. Users will see the child's photo and they will be able to "agree"
with a current suggested name or post their own suggested name. I know most parents would rather name
their own kid..this is just for fun to see what I can accomplish. :)
So far I have two tables like this:
kidID (int unique primary key)
parentId (int..cross referenced to parents account)
photoFile (varchar 25 characters referencing file photo)
nameId (int unique primary key)
suggestedName (varchar 15 characters)
votescast (int...this counts how many votes each name has)
What I would like is to have a "masterlist" page that shows a list of all current photos with the
currently top voted name. So if newborn.jpg has 25 votes for "Sammy" and littlecutie.jpg has 3 votes
for "Jessica" my query would return the fields "KidId, photoFile, suggestedName, VotesCast" with the
5, redheadnewborn.jpg, "Sammy", 25
12, littlecutie.jpg, "Jessica", 3
I am only interested in seeing the top voted name for each photo. I have tried a number of queries
involving views...none of which seem to return what I want.
I know this will probably involve the sql count function but I can't find an example that is close to what I want. Any help would be very appreciated. If it helps I am using MS SQL 2008.