help with SQL query / view / count problem....

[jasperjerome]

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:

Kids:
kidID (int unique primary key)
parentId (int..cross referenced to parents account)
photoFile (varchar 25 characters referencing file photo)

Votes:
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

date being:

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.

Thanks,
J

[sknake]

Odd database design but you could do something like:

IF OBJECT_ID('Kids_Test', 'U') IS NOT NULL DROP TABLE Kids_Test
IF OBJECT_ID('Votes_Test', 'U') IS NOT NULL DROP TABLE Votes_Test
GO
CREATE TABLE Kids_Test
(
  KidID int identity(1000, 1) PRIMARY KEY,
  ParentId int,
  PhotoFile varchar(25)
)

CREATE TABLE Votes_Test
(
  NameId int identity(1000, 1) PRIMARY KEY, --is this the voters id?
  KidId int, --kid this relates to
  SuggestedName varchar(15),
  --VotesCast int --You shouldn't store a bucket total in a table like that. You will probably want
  --to store individual votes so you can log IP addr of who voted, etc etc
)
GO
Declare @ID int
Insert Into Kids_Test (ParentId, PhotoFile) Values (1, 'redheadnewborn.jpg')
SET @ID = Cast(SCOPE_IDENTITY() as int)
Insert Into Votes_Test (KidId, SuggestedName) Values (@ID, 'Sammy')
Insert Into Votes_Test (KidId, SuggestedName) Values (@ID, 'Sammy')
Insert Into Votes_Test (KidId, SuggestedName) Values (@ID, 'Sammy')
Insert Into Votes_Test (KidId, SuggestedName) Values (@ID, 'Sammy')
Insert Into Votes_Test (KidId, SuggestedName) Values (@ID, 'Sammy')
Insert Into Votes_Test (KidId, SuggestedName) Values (@ID, 'Sammy')
Insert Into Votes_Test (KidId, SuggestedName) Values (@ID, 'Sammy')
Insert Into Votes_Test (KidId, SuggestedName) Values (@ID, 'Sammy')
Insert Into Votes_Test (KidId, SuggestedName) Values (@ID, 'Sammy')
Insert Into Votes_Test (KidId, SuggestedName) Values (@ID, 'Sammy')
Insert Into Votes_Test (KidId, SuggestedName) Values (@ID, 'Sammy')
Insert Into Votes_Test (KidId, SuggestedName) Values (@ID, 'Sammy')
Insert Into Votes_Test (KidId, SuggestedName) Values (@ID, 'Sammy')
Insert Into Votes_Test (KidId, SuggestedName) Values (@ID, 'Sammy')
Insert Into Votes_Test (KidId, SuggestedName) Values (@ID, 'Sammy')
Insert Into Votes_Test (KidId, SuggestedName) Values (@ID, 'Sammy')
Insert Into Votes_Test (KidId, SuggestedName) Values (@ID, 'Sammy')
Insert Into Votes_Test (KidId, SuggestedName) Values (@ID, 'Sammy')
Insert Into Votes_Test (KidId, SuggestedName) Values (@ID, 'Sammy')
Insert Into Votes_Test (KidId, SuggestedName) Values (@ID, 'Sammy')
Insert Into Votes_Test (KidId, SuggestedName) Values (@ID, 'Sammy')
Insert Into Votes_Test (KidId, SuggestedName) Values (@ID, 'Sammy')
Insert Into Votes_Test (KidId, SuggestedName) Values (@ID, 'Sammy')
Insert Into Votes_Test (KidId, SuggestedName) Values (@ID, 'Sammy')
Insert Into Votes_Test (KidId, SuggestedName) Values (@ID, 'Sammy')
Insert Into Votes_Test (KidId, SuggestedName) Values (@ID, 'SomeoneElse')
Insert Into Kids_Test (ParentId, PhotoFile) Values (1, 'littlecutie.jpg')
SET @ID = Cast(SCOPE_IDENTITY() as int)
Insert Into Votes_Test (KidId, SuggestedName) Values (@ID, 'Jessica')
Insert Into Votes_Test (KidId, SuggestedName) Values (@ID, 'Jessica')
Insert Into Votes_Test (KidId, SuggestedName) Values (@ID, 'Jessica')
Insert Into Votes_Test (KidId, SuggestedName) Values (@ID, 'Bob')

Select *,
(
  Select Top 1 SuggestedName
  From Votes_Test
  Where Votes_Test.KidId = Kids_Test.KidId
  Group By KidId, SuggestedName
  Order By Count(*) Desc
) As SuggestedName,
(
  Select Top 1 Count(*)
  From Votes_Test
  Where Votes_Test.KidId = Kids_Test.KidId
  Group By KidId, SuggestedName
) As Cnt
From Kids_Test

[kool.net]

hey simply add a new field in votes table as a foreign key of kids
like kid_id.
then in your query u sort you results with votescast.
Just think about dat........ its not a big issue
ok
have a gr8 day with sql

[kool.net]

your query shud b like

select kids.kidID , kids.PhotoFile , votes.kid_id, votes.SuggestedName , votes.votescast from kids, votes where kids.KidID = votes.kid_id order by votes.votescast DESC";