SQL for dummies

[R-Tape]

Yep that was the issue. Thanks fellas. The title of this thread definitely applies here!

[R-Tape]

I have this working SQL code for new magrefs:

Code: Select all

insert into magrefs(id, referencetype_id, entry_id, label_id, topic_id, issue_id, page, is_supplement, score) values(222362, 0, 36228, NULL, NULL, 612, 19, 0, NULL);

What is the easiest way to make the code choose the next id up? Magrefs are not grouped by property, so I think it should just be max(id)+1.

I tried this but it didn't work:

Code: Select all

...values(select max(id)+1 from magrefs...

I also modified some of Einar's SQL that works for labels (which I don't understand), but it didn't work either.

Code: Select all

(select k from (select max(id)+1 as k from magrefs) as x)

Any suggestions?

[AndyC]

It would need extra brackets round the subquery

Values((select max(Id)+1), ...

Assuming it's not an identity column

[R-Tape]

It would need extra brackets round the subquery

Values((select max(Id)+1), ...

Assuming it's not an identity column

Tried that. I get the error: Table 'magrefs' is specified twice, both as a target for 'INSERT' and as a separate source for data. Does that mean it's an identity column, and if so, how to handle this?

[Einar Saukas]

You can simply omit ID since it's an identity column:

Code: Select all

insert into magrefs(referencetype_id, entry_id, label_id, topic_id, issue_id, page, is_supplement, score) values(0, 36228, NULL, NULL, 612, 19, 0, NULL);

[R-Tape]

If I wanted to query ZXDB for all text adventures released in 2010, how do I go about it, and why the hell doesn't this work?

SELECT * FROM entries
where id in (SELECT entry_id FROM releases WHERE release_year = 2010)
AND genretype_id = 5 OR genretype_id = 6;

NB genretype_id 5 is text only, 6 is text illustrated.

[PeterJ]

I'm not in front of my computer [mention]R-Tape[/mention], but try putting the clause in brackets:

Code: Select all

(genretype_id = 5 OR genretype_id = 6)

If you don't then the Or is being applied to the whole query.

You could also use In, so

Code: Select all

gentype_id in (5,6)

[R-Tape]

I'm not in front of my computer @R-Tape, but try putting the clause in brackets:

That's it! Thanks Peter.

[PeterJ]

Glad it helped. That sort of thing has caught me out many a time!

[redballoon]

Wait, so ID in the ENTRIES table = ENTRY_ID in the RELEASES table?

[PeterJ]

Wait, so ID in the ENTRIES table = ENTRY_ID in the RELEASES table?

Yes, then there is a release sequence for each release.

[redballoon]

I’m not sure what flavour of SQL you’ll be using but I’d probably write the following query

Code: Select all

select e.*
from entries e
    ,releases r
where r.entry_id = e.id
  and r.release_year = 2010
  and e.genretype_id in (5,6)

That’s just doing an inner join between the 2 tables and probably have better performance.

[R-Tape]

...but I’d probably write the following query

(I'll sit down and learn SQL properly. Next year)

[Morkin]

...Didn't want to create a new thread, figured this one would do.

Can someone tell/remind me how you'd search for 'non-crap only' (as in not CSSCGC) games only in ZXDB, using SQL?

I was under the impression that there was an 'is crap' flag/column somewhere, but I can't find it.

[PeterJ]

Hi @Morkin,

@Einar Saukas provides us with an extra piece of SQL I run after importing ZXDB which creates some specific tables such as entries including a crap game flag. I will email you in the morning.

Peter

[R-Tape]

I was under the impression that there was an 'is crap' flag/column somewhere, but I can't find it.

That's how it used to be, but now the CGC is a 'tag' (formerly group). They aren't all neatly id-x to id-y either, but they all start with "CSSCGC Crap Games Contest". If you know how to search in entries where they are not in a tag containing "CSSCGC Crap Games Contest" then it might work(if you want the thrill of the chase before seeing Einar's code).

I really need to sit down and actually learn this stuff...

[Morkin]

Thanks both - I can see 'CSSCGC' appearing in the 'tags' table. Was wondering where it was.

(if you want the thrill of the chase before seeing Einar's code).

...It'd be more "ennui-of-the-failure"... It's just for personal use, so I'll wait to see how someone a bit more knowledgeable filters out the entry_id, my SQL is a bit rubbish.

[R-Tape]

...It'd be more "ennui-of-the-failure"...

Bienvenue la SQL!

[Morkin]

Thanks @PeterJ for the email.

Rather than rebuild all the tables adding [CSSCGC] at the start of the entries, I think I've picked out the condition for 'filter the crap'. Something like this:

Code: Select all

SELECT e.* FROM entries e
 WHERE e.id NOT IN (select m.entry_id FROM members m INNER JOIN tags g ON g.id = m.tag_id AND g.name LIKE 'CSSCGC Crap Games Contest%');

[Morkin]

Welcome to "Morkin's beginner SQL hour".

Gave myself the challenge of finding games inspired by Atic Atac (entries table id = 9305). Just because it's checkable on the website.

Code: Select all

SELECT r.original_id AS inspiration_game_id,
       (
           SELECT title FROM entries e
           WHERE e.id = r.original_id
       )
       AS inspiration_game_name,
       e.id AS inspired_game_id,
       e.title AS inspired_game_title
FROM   entries e,
       relations r
WHERE  e.id = r.entry_id AND 
       r.relationtype_id = 'i' AND 
       r.original_id = 9305;

The nested 'SELECT' isn't necessary for the results, but I wanted to include the inspiration game name ('Atic Atac') in the list, not just its ID. Those 3 lines took me about an hour to figure out

There may be a better way, but I think it works so I'm now going to reward myself with a toasted waffle...

[PeterJ]

Have extra toppings for using nesting @Morkin!

[Morkin]

Have extra toppings for using nesting @Morkin!

Ah - missed this directive, but the peanut butter topping was fine...

[Morkin]

Probably something I'm missing.

With the ZXDB updates that get announced, what's the best way of keeping my ZXDB copy up to date with the incremental changes?

I think I may have missed the last couple of updates but am not sure how to check which ones - Einar has a bunch of 'incremental update' SQL files available but I'm not sure if there are dates tying them into the posts on that ZXDB updates thread?

I think I downloaded ZXDB in May so hopefully haven't missed too much.

[R-Tape]

With the ZXDB updates that get announced, what's the best way of keeping my ZXDB copy up to date with the incremental changes?

I don't know if there's another way, but I just drop the old DB and rebuild it every time.

In Heidi SQL, in the LH column, I right click on ZXDB and 'drop' the old one. Then File > Run SQL file > and select the new ZXDB_mysql.sql.

Not sure if that's what you're after.

[Morkin]

I don't know if there's another way, but I just drop the old DB and rebuild it every time.

I used the original script to build it in SQLiteStudio last time. Only thing is it couldn't cope with running the whole thing so I had to break it into separate bits and run them individually. In fact most table INSERTs I had to break up into loads of separate inserts - the whole process took me about a week, so I'm not doing that again..

I guess I could wade through the SQL update script files to see what's been added/changed, and at some point I'll probably hit one that's already reflected in my copy. Then I can just run them from there. But there didn't seem to be anything in the files (comments etc.) indicating which update it is, or what date (I'm assuming they are incrementally in date order).