Selecting and Inserting from different tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Selecting and Inserting from different tables

Author

Topic

tomm098
Starting Member

3 Posts

Posted - 2009-08-19 : 12:19:09

Hey all. I'm having some problems with some kinda advanced SQL. This is my dilemma. I have two tables. Music and Votes. Each entry in 'Music' has a unique ID. The 'Music' table also has a row called 'Average Vote'. I was hoping there was a way to average the 'Votes' in the 'Votes' table and display it in the 'Music' table under 'Average Vote'

It's kinda confusing I know, but I cant seem to use the UNION or SELECT INTO statement. How should I go about this? The table structure is this by the way.

'Music' : Music ID, Title, Artist, User, Average Vote
'Votes' : Vote, Music ID, User

Thanks very much.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-08-19 : 13:02:57

[code]UPDATE A
SET A.AverageVote = B.AverageVote
FROM Music A
INNER JOIN (SELECT MusicID,AVG(Vote) as AverageVote from Votes group by Music) B
on A.MusicID = B.MusicID[/code]

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-08-19 : 13:57:29

That should probably read "group by [Music ID]" rather than "group by music".

An infinite universe is the ultimate cartesian product.

tomm098
Starting Member

3 Posts

Posted - 2009-08-19 : 22:18:28

Thanks a lot for your reply. I'm trying to work on the code you gave me. Are those A and B values how I should write it into SQL, or are they variables I must replace. I seem to be getting error codes.

What code would i need to simply display the Average Votes in `Music`, from the values in `Votes` ? Is it that easy?

Thanks a lot in advance.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-08-20 : 09:00:35

What do you mean by "display"?

The above query will update your "Music" table with the average votes from your "Vote" table

And..A and B are table aliases...and are needed in the SQL query.

tomm098
Starting Member

3 Posts

Posted - 2009-08-20 : 10:43:10

I think I may have been a lil unclear initially. I feel kinda bad. Hey Vijay, would you be interested in contacting me to help me with this. I need temporary constant help to get this feature online. I'm willing to pay you. This is it.

I need a system. Where the users vote on the songs. Then that vote gets sent/updated to the Vote table. But at the same time. The average vote is displayed next to each corresponding Music entry. The music table might need to columns, Submitted Vote, and Average Vote. I'm not sure. If you think you could handle this you can post back here. Or my email is tomm098@hotmail.com

In a nutshell. I would like each song to have a vote/rating.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-08-20 : 11:08:00

try reading the HOW TO ASK A QUESTION link in my sig, and post again.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources