5 Replies - 378 Views - Last Post: 19 May 2013 - 08:36 AM Rate Topic: -----

#1 IceHot  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 199
  • Joined: 28-August 12

Creating a view that has 5 rows with pure SQL

Posted 18 May 2013 - 03:07 PM

My only exposure to SQL (or even non-flat databases in general!) has been through my current personal Java project. In this project, I am making a math game with a database for storage. I have decided to use two tables for the records themselves (one for each game mode), and two views (one to correspond to each table). The tables will be storing the difficulty level (an int), the completion time, the number of problems the user got right, and the number they got wrong. My problem, however, was with how to go about making this view. I would like for it to, for example, count the number of times they have played at a certain difficulty, while containing the difficulty itself. The easy (but tedious!) answer would be to use a
SELECT count(Difficulty), min(Time), max(Time), avg(Time), sum(NumberRight), sum(NumberWrong) FROM (SELECT * FROM IntegerModeData WHERE Difficulty=rowNumber AS CS
FIVE FREAKING TIMES! The other answer I was thinking of would be to make a table with just the difficultyNumber and the difficultyName, and use a JOIN. The second option seems more tempting, but I was wanting to know which one would YOU choose and why?

Oh, and does a JOIN search a whole table for a match between the two tables?

This post has been edited by IceHot: 18 May 2013 - 03:19 PM


Is This A Good Question/Topic? 0
  • +

Replies To: Creating a view that has 5 rows with pure SQL

#2 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 9097
  • View blog
  • Posts: 34,181
  • Joined: 12-June 08

Re: Creating a view that has 5 rows with pure SQL

Posted 18 May 2013 - 04:32 PM

First off - why do you think you need a few for each table? Are you planning on changing the database structure much? Have a specific, and complex, query to run each time? Need some sort of security for a webservice?

Quote

I have decided to use two tables for the records themselves (one for each game mode),

I am not sure if I follow. What is a 'game mode' and why not just have it be an integer column for the data entry? Solo gaming = 0, head to head = 1, etc.. Then you only need to worry about one table.

Quote

I would like for it to, for example, count the number of times they have played at a certain difficulty, while containing the difficulty itself. The easy (but tedious!) answer would be to use a

Why not use the group by statement? Group by user id, and difficulty.

Quote

FIVE FREAKING TIMES! The other answer I was thinking of would be to make a table with just the difficultyNumber and the difficultyName, and use a JOIN.

Huh?

Quote

Oh, and does a JOIN search a whole table for a match between the two tables?

A join says 'here are two tables that I want to link up by one to many columns so I can get data on like rows'.

http://www.codinghor...-sql-joins.html
Was This Post Helpful? 3
  • +
  • -

#3 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3371
  • View blog
  • Posts: 11,420
  • Joined: 12-December 12

Re: Creating a view that has 5 rows with pure SQL

Posted 18 May 2013 - 04:43 PM

View Postmodi123_1, on 18 May 2013 - 11:32 PM, said:


That's an interesting page. (I had considered Venn diagrams myself but never bothered to draw them ;).)

[Off topic] It also lead to my phrase of the day: mouse ballistics!
Was This Post Helpful? 0
  • +
  • -

#4 IceHot  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 199
  • Joined: 28-August 12

Re: Creating a view that has 5 rows with pure SQL

Posted 19 May 2013 - 01:05 AM

Must I spell it all out?

Quote

First off - why do you think you need a few for each table? Are you planning on changing the database structure much? Have a specific, and complex, query to run each time? Need some sort of security for a webservice?

This game is currently in beta-testing mode: I am moving all data to a database, and this question is about how to handle it so that they could screencap the data and send it back to me. What they are to screencap should look like this: http://imgur.com/N8MY5Om . Oh, and what are you talking about when you say "a few for each table". Are you talking about the fixed row count? Or do you mean "view"? If you do mean "view", I am choosing to use one because it provides up-to-date information that is all the high scores. I accidentally used a table, but learned why that would be a bad move.

Quote

I am not sure if I follow. What is a 'game mode' and why not just have it be an integer column for the data entry? Solo gaming = 0, head to head = 1, etc.. Then you only need to worry about one table.

My math game has two modes (problem types). See the tabs in the screenshot. (In there, I have saved/loaded the data by Java's object serialization, which is why I am trying to move it to a database.)

Quote

I am not sure if I follow. What is a 'game mode' and why not just have it be an integer column for the data entry? Solo gaming = 0, head to head = 1, etc.. Then you only need to worry about one table.

Possibly something to consider for the future when I move to a remote server. Right now, I am simply having people play the game and generate real data.

Quote

Why not use the group by statement? Group by user id, and difficulty.

See response above. Every user will, at this point, have their own database, so there will be no UserID to worry about right now. The GROUP BY clause may be just what the programmer ordered. Why the heck didn't I see that earlier? //Should have done more research...

You just might have answered my question! Thank you so much!! //Time to get some practice with that statement before moving on....
Was This Post Helpful? -2
  • +
  • -

#5 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5805
  • View blog
  • Posts: 12,644
  • Joined: 16-October 07

Re: Creating a view that has 5 rows with pure SQL

Posted 19 May 2013 - 04:53 AM

Unfortunate. I'd thought to try to answer this, but, damn.

What people need to help usually just two things. What you have and where you're trying to go with it. For a database question, this is usually quite simple. You show a sample of the data in various tables, a sample of what you want to data to look like, and what you've tried to get there.

What you do NOT do is pull a fucking attitude when the only thing you've bother to show are your inherent flailings.
Was This Post Helpful? 1
  • +
  • -

#6 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 9097
  • View blog
  • Posts: 34,181
  • Joined: 12-June 08

Re: Creating a view that has 5 rows with pure SQL

Posted 19 May 2013 - 08:36 AM

**Woke up a bit ago, so this is posted after the lock.. A few issues and misconceptions that need to be cleared up. **

View PostIceHot, on 19 May 2013 - 03:05 AM, said:

Must I spell it all out?

Quote

First off - why do you think you need a few for each table? Are you planning on changing the database structure much? Have a specific, and complex, query to run each time? Need some sort of security for a webservice?

This game is currently in beta-testing mode: I am moving all data to a database, and this question is about how to handle it so that they could screencap the data and send it back to me. What they are to screencap should look like this: http://imgur.com/N8MY5Om . Oh, and what are you talking about when you say "a few for each table". Are you talking about the fixed row count? Or do you mean "view"? If you do mean "view", I am choosing to use one because it provides up-to-date information that is all the high scores. I accidentally used a table, but learned why that would be a bad move.

Yes, the 'few' meant views. See a view is just nothing but a gussied up way to call a table essentially making a 'virtual table'. Those questions I asked were typical reasons why you would use a view and not, say, a stored procedure or just a regular sql query. From what I read they didn't seem necessary, but I wasn't sure what the motivation for using them was.

http://msdn.microsof...=sql.80%29.aspx



View PostIceHot, on 19 May 2013 - 03:05 AM, said:

Quote

I am not sure if I follow. What is a 'game mode' and why not just have it be an integer column for the data entry? Solo gaming = 0, head to head = 1, etc.. Then you only need to worry about one table.

My math game has two modes (problem types). See the tabs in the screenshot. (In there, I have saved/loaded the data by Java's object serialization, which is why I am trying to move it to a database.)

Sure, but there is zero reason you would have two tables then. If all the rest of the data is the same - and the only difference between the two tables is this 'mode' then add a column in one table and be done with it. You need to start thinking in sets of like data when rolling with SQL. It is a more efficient and clean approach to represent your data in one table and not two that do, essentially, the same thing.


View PostIceHot, on 19 May 2013 - 03:05 AM, said:

Quote

Why not use the group by statement? Group by user id, and difficulty.

See response above. Every user will, at this point, have their own database, so there will be no UserID to worry about right now. The GROUP BY clause may be just what the programmer ordered. Why the heck didn't I see that earlier? //Should have done more research...

You just might have answered my question! Thank you so much!! //Time to get some practice with that statement before moving on....


'Every user will have their own database' is a horribly brittle idea. Again - when dealing with SQL group your data so it is not in some brittle format and is flexible enough to have table and column changes later. Having every user with their own table is *not* a robust solution, but having a 'user id' table and then your 'scores' table have a column for a user's id is flexible. That means new users do not need a new table each time they join, and all the future queries can limit on the user id. Two tables, and not infinite tables.
Was This Post Helpful? 2
  • +
  • -

Page 1 of 1