Looking for a SQL guru

[myshkin]

Magnate: Im not getting any smarter from the src/stats directory...looks more like some db driver capabilities.

FYI, the schema is very lightly documented in main-stats.c; it is basically a direct translation of the consumables and wearables structs, plus dumps of the lib/edit/*.txt files. In the future, I hope to move that section of the code into src/stats/.

[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.

[NinjaN]

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

[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 ...

[NinjaN]

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

[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?

[Derakon]

In any case, thanks for the correction; I hadn't realized that LIMIT was specific to MySQL. My mistake.

[Magnate]

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.

[NinjaN]

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

[Derakon]

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.

[Magnate]

@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.

[NinjaN]

Is the goal to have optimized queries, or just returning valid results?

This topic starts to drift to SQL optimization, not helping get "the results" ;-)

I think the goal is a good mix of both! You can have valid results which execute horribly slow and complicated, sure. But why not optimize your queries if you know how to do it?

I didn't realize CASE could be a good way to do it but it surely is. I've even incorporated that knowledge into my work (you know, the paid one) and impressed some collegues with it's elegance... ;-P


@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!


Cheers, NinjaN

[Psi]

Is the goal to have optimized queries, or just returning valid results?

This topic starts to drift to SQL optimization, not helping get "the results" ;-)

Doesn't the query above do exactly what Magnate asked for in the original post? The fact it is clearer to read and quicker to run is surely a good thing...

[Azerath]

Is the goal to have optimized queries, or just returning valid results?

This topic starts to drift to SQL optimization, not helping get "the results" ;-)

[Psi]

I'm a DBA rather than a SQL developer, but for queries like that I go for the sum(case...) structure like Xaxyx has posted.