Angband.oook.cz
Angband.oook.cz
AboutVariantsLadderForumCompetitionComicScreenshotsFunniesLinks

Go Back   Angband Forums > Angband > Development

Reply
 
Thread Tools Display Modes
Old September 5, 2011, 17:30   #21
Magnate
Angband Devteam member
 
Join Date: May 2007
Location: London, UK
Posts: 5,057
Magnate is on a distinguished road
Send a message via MSN to Magnate Send a message via Yahoo to Magnate
Quote:
Originally Posted by NinjaN View Post
@Magnate:
As you can perhaps see here, may ways lead to Rome. Ask 3 people the exactly same question "how do I do this using SQL" and you are sure to get at least 4 different answers.

In the end it's up to you which way you want to go. In general, the most simple to read query is the one which executes fastest, at least to my experience. If it does what you want in a way you can understand and in a time you are willing to wait, then it's a good query!
Thank you all for the responses and help. At the moment I'm just struggling to get any meaningful results at all - I'll be more than happy to have my queries optimised once I start getting them! Thanks for all the support so far.
__________________
"3.4 is much better than 3.1, 3.2 or 3.3. It still is easier than 3.0.9, but it is more convenient to play without being ridiculously easy, so it is my new favorite of the versions." - Timo Pietila
Magnate is offline   Reply With Quote
Old September 5, 2011, 20:33   #22
Derakon
Prophet
 
Derakon's Avatar
 
Join Date: Dec 2009
Posts: 8,827
Derakon is on a distinguished road
One trick you can do with your queries to speed them up is to attach "LIMIT 100" (or LIMIT 1000, etc.) to the end. This can speed up the query process by making the database only generate a few rows of output; it will stop after it's done. Obviously this isn't useful for real queries, but when you're trying to figure out how to get the information you want, it can be helpful since you don't actually care about more than a few rows.
Derakon is offline   Reply With Quote
Old September 6, 2011, 11:12   #23
NinjaN
Rookie
 
Join Date: Aug 2011
Location: Münster, Germany
Posts: 7
NinjaN is on a distinguished road
Be careful here: LIMIT is not part of the standard SQL syntax.

One important thing to know: There're many "accents" of SQL! They all have a defined set of common keywords but each "accent" adds it's own flavour.

LIMIT is one such example. It's not part of the common keywords (often called "SQL92"), it's part of (I think) MySQL's set of added keywords. These don't necessarily have counterparts in other "accents". LIMIT (MySQL) has a counterpart called TOP (MS SQL) and possibly others. So you have to know which added keywords your database supports, which is often found in it's documentation.



Cheers, NinjaN
NinjaN is offline   Reply With Quote
Old September 6, 2011, 20:17   #24
Magnate
Angband Devteam member
 
Join Date: May 2007
Location: London, UK
Posts: 5,057
Magnate is on a distinguished road
Send a message via MSN to Magnate Send a message via Yahoo to Magnate
Quote:
Originally Posted by NinjaN View Post
Be careful here: LIMIT is not part of the standard SQL syntax.

One important thing to know: There're many "accents" of SQL! They all have a defined set of common keywords but each "accent" adds it's own flavour.

LIMIT is one such example. It's not part of the common keywords (often called "SQL92"), it's part of (I think) MySQL's set of added keywords. These don't necessarily have counterparts in other "accents". LIMIT (MySQL) has a counterpart called TOP (MS SQL) and possibly others. So you have to know which added keywords your database supports, which is often found in it's documentation.



Cheers, NinjaN
Your English is extremely good, but the word you're looking for is dialect, rather than accent. But thank you for the warning. We are actually using sqlite3, which I imagine is another slightly different dialect.
__________________
"3.4 is much better than 3.1, 3.2 or 3.3. It still is easier than 3.0.9, but it is more convenient to play without being ridiculously easy, so it is my new favorite of the versions." - Timo Pietila
Magnate is offline   Reply With Quote
Old September 6, 2011, 20:43   #25
Derakon
Prophet
 
Derakon's Avatar
 
Join Date: Dec 2009
Posts: 8,827
Derakon is on a distinguished road
In any case, thanks for the correction; I hadn't realized that LIMIT was specific to MySQL. My mistake.
Derakon is offline   Reply With Quote
Old November 2, 2011, 16:41   #26
Magnate
Angband Devteam member
 
Join Date: May 2007
Location: London, UK
Posts: 5,057
Magnate is on a distinguished road
Send a message via MSN to Magnate Send a message via Yahoo to Magnate
So, I now know quite a bit more about SQL than I did a couple of months ago. Here's my first detailed question:

I have two tables:

wearables_dam contains level, k_idx (the object type), to_d and count
wearables_flags contains level, k_idx, of_idx (the flag index) and count

Is the data in these tables related? Is it possible to construct a query which says SELECT something FROM both tables WHERE to_d = 5 AND of_idx = 10 GROUP BY k_idx ORDER BY SUM(count) ... or something? i.e. tell me about all the items which have +5 to dam *and* this particular flag?
__________________
"3.4 is much better than 3.1, 3.2 or 3.3. It still is easier than 3.0.9, but it is more convenient to play without being ridiculously easy, so it is my new favorite of the versions." - Timo Pietila
Magnate is offline   Reply With Quote
Old November 2, 2011, 17:04   #27
NinjaN
Rookie
 
Join Date: Aug 2011
Location: Münster, Germany
Posts: 7
NinjaN is on a distinguished road
Hallo again,


nice to see you are still learning to use SQL (so do I on a daily basis)... ;-P

So, let's answer question after question.

Are these tables related? I can't tell. Are they relatable? Hell yes!
All you need is one column in both tables which works as a link between those tables. Most of the time, this will be a number. Here, k_idx seems to be the link.

In "traditional" databases, those links always work the same way. Imagine this:
you have a table "person" and a table "country". You want to save which person lives in which country. The table "country" has all the countries (D'OH) and each entry has a unique identifier. This is called the "primary key" of that table.
In the table "person" you have some columns for each person's name, address and so on. But instead of saving the country's name in "person", you just link into that other table by adding a column containing that country's primary key. In the table "person", that is called a "foreign key".
But you don't want some number, you want to see the real name of the country. That's where SQL comes into play! With some SELECT-magic you can build a query which gives you exactly that information:

SELECT *
FROM person JOIN country ON person.contry = country.id

That's it. You just joined two tables, effectively producing one resultset. On this resultset all the other rules apply. You can filter (WHERE), sort (ORDER BY) and group (GROUP BY), whatever comes to mind...
JOIN is quite a mighty keyword. By default, it will only give you results it can find in both tables. Should there be a person with a country identifier which doesn't exist, that person will not be part of the resultset. But don't fear, for these cases there's LEFT OUTER JOIN, RIGHT OUTER JOIN or even FULL OUTER JOIN.

I suggest reading into that keyword to get a better understanding, this is just the tip of the iceberg...


Cheers, NinjaN
NinjaN is offline   Reply With Quote
Old November 2, 2011, 17:11   #28
Magnate
Angband Devteam member
 
Join Date: May 2007
Location: London, UK
Posts: 5,057
Magnate is on a distinguished road
Send a message via MSN to Magnate Send a message via Yahoo to Magnate
Hmmm. Thank you, again - this is going to be more complex than I thought. Neither of those two wearables_ tables has a primary key. There is a table called object_info which has k_idx as its primary key, so I assume that I will need to join both wearables_ tables to that table using that key.

Well, at least I won't get bored ...
__________________
"3.4 is much better than 3.1, 3.2 or 3.3. It still is easier than 3.0.9, but it is more convenient to play without being ridiculously easy, so it is my new favorite of the versions." - Timo Pietila
Magnate is offline   Reply With Quote
Old November 2, 2011, 17:20   #29
NinjaN
Rookie
 
Join Date: Aug 2011
Location: Münster, Germany
Posts: 7
NinjaN is on a distinguished road
Not so fast, young padawan! ;-D


You don't need primary keys for JOIN to work. In fact, JOIN works with all columns. As long as you have some column (or a combination of columns) linking these tables, you are good to go.

Say you want to link using the columns k_idx and level:

SELECT *
FROM wearables_dam dam contains JOIN wearables_flags flags
ON dam.k_idx = flags.k_idx AND dam.level = flags.level

Here, two things are to note:
1) JOIN can link using two or more columns using the AND keyword
2) You should use ALIAS names for your tables. Since columns of the same name exist in both tables, you have to prepend the table names. I'm lazy, so I use shorter ALIAS names (dam and flag)

So you just have to identify which columns can be used for joining these two tables!


NinjaN
NinjaN is offline   Reply With Quote
Old November 2, 2011, 17:34   #30
pav
Administrator
 
pav's Avatar
 
Join Date: Apr 2007
Location: Prague, Czech republic
Age: 39
Posts: 779
pav is on a distinguished road
Send a message via ICQ to pav
I don't think you can get answer to your question from these data. These tables looks like a result of an aggregation and the link for individual items is already lost. You need either query table of all generated items directly, or build another aggregated intermediate that does not lose the relation.
__________________
See the elves and everything! http://angband.oook.cz
pav is offline   Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT +1. The time now is 08:32.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.