MS SQL

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

Please support our MS SQL advertiser: Intel Parallel Studio Home

•

Join Date: Oct 2009

Posts: 1

Reputation: jasperjerome is an unknown quantity at this point

Solved Threads: 0

jasperjerome jasperjerome is offline

Offline

Newbie Poster

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

Oct 12th, 2009

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

•

Join Date: Feb 2009

Posts: 3,410

Reputation: sknake has much to be proud of

Solved Threads: 615

sknake

Offline

.NET Enthusiast

Oct 13th, 2009

Odd database design but you could do something like:

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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
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

Scott Knake
Custom Software Development
Apex Software, Inc.

•

Join Date: Oct 2009

Posts: 27

Reputation: kool.net is an unknown quantity at this point

Solved Threads: 0

kool.net

Offline

Light Poster

Oct 16th, 2009

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

•

Join Date: Oct 2009

Posts: 27

Reputation: kool.net is an unknown quantity at this point

Solved Threads: 0

kool.net

Offline

Light Poster

Oct 16th, 2009

your query shud b like

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
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";
 
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";

Last edited by peter_budo; Oct 17th, 2009 at 1:00 pm. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks).

Thread Tools	Search this Thread
Show Printable Version Email this Page

DaniWeb Message

Cancel Changes