NB genretype_id 5 is text only, 6 is text illustrated.SELECT * FROM entries
where id in (SELECT entry_id FROM releases WHERE release_year = 2010)
AND genretype_id = 5 OR genretype_id = 6;
SQL for dummies
Moderator: druellan
Re: SQL for dummies
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?
Re: SQL for dummies
I'm not in front of my computer [mention]R-Tape[/mention], but try putting the clause in brackets:
If you don't then the Or is being applied to the whole query.
You could also use In, so
Code: Select all
(genretype_id = 5 OR genretype_id = 6)
You could also use In, so
Code: Select all
gentype_id in (5,6)
Re: SQL for dummies
That's it! Thanks Peter.
Re: SQL for dummies
Glad it helped. That sort of thing has caught me out many a time!
-
- Manic Miner
- Posts: 392
- Joined: Sun Nov 12, 2017 3:54 pm
Re: SQL for dummies
Wait, so ID in the ENTRIES table = ENTRY_ID in the RELEASES table?
Re: SQL for dummies
Yes, then there is a release sequence for each release.redballoon wrote: ↑Fri Nov 27, 2020 11:10 am Wait, so ID in the ENTRIES table = ENTRY_ID in the RELEASES table?
-
- Manic Miner
- Posts: 392
- Joined: Sun Nov 12, 2017 3:54 pm
Re: SQL for dummies
I’m not sure what flavour of SQL you’ll be using but I’d probably write the following query
That’s just doing an inner join between the 2 tables and probably have better performance.
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)
Re: SQL for dummies
(I'll sit down and learn SQL properly. Next year)
Re: SQL for dummies
...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.
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.
My Speccy site: thirdharmoniser.com
Re: SQL for dummies
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
@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
Re: SQL for dummies
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...
Re: SQL for dummies
Thanks both - I can see 'CSSCGC' appearing in the 'tags' table. Was wondering where it was.
...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.
My Speccy site: thirdharmoniser.com
Re: SQL for dummies
Bienvenue la SQL!
Re: SQL for dummies
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:
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%');
My Speccy site: thirdharmoniser.com
Re: SQL for dummies
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.
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...
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...
My Speccy site: thirdharmoniser.com
Re: SQL for dummies
Have extra toppings for using nesting @Morkin!
Re: SQL for dummies
Ah - missed this directive, but the peanut butter topping was fine...
My Speccy site: thirdharmoniser.com
Re: SQL for dummies
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.
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.
My Speccy site: thirdharmoniser.com
Re: SQL for dummies
I don't know if there's another way, but I just drop the old DB and rebuild it every time.Morkin wrote: ↑Tue Jun 14, 2022 11:38 pm With the ZXDB updates that get announced, what's the best way of keeping my ZXDB copy up to date with the incremental changes?
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.
Re: SQL for dummies
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).
My Speccy site: thirdharmoniser.com
Re: SQL for dummies
This where I start to worry again that I'm not 'doing ZXDB' in the proper way. My laptop is bog standard, and it runs the whole SQL file in about 30 seconds in Heidi. Is SQLite studio have a really fancy interface or something?Morkin wrote: ↑Wed Jun 15, 2022 10:41 am 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..
Re: SQL for dummies
I have a small Powrershell script that can detect and download the latest release using a local GIT repository, remove the old database and load the new one (plus my own changes). I can share it you are on Windows.
Re: SQL for dummies
I think it's probably the opposite, a 'lite' version? If nobody else has any issues I suspect mine are caused by me trying to run that 90MB .sql script within the app itself perhaps.
Ooh yes please, I'm on Windows. I guess that may be a more efficient way to remove/replace the DB then? I know about as much Powershell as I do SQL, but if it does it fairly quickly, I'm all in...
My Speccy site: thirdharmoniser.com
Re: SQL for dummies
Code: Select all
Write-Host ">>> Downloading the latest ZXdb sql dump"
$req = Invoke-Webrequest -Uri https://github.com/zxdb/ZXDB/raw/master/ZXDB_mysql.sql -Outfile ZXDB_mysql.sql -PassThru -UseBasicParsing
if ( $req.StatusCode -eq 200 ) {
Write-Host ">>> Cleaning the local database"
mysql -uroot -p -h 127.0.0.1 -e "DROP DATABASE ZXDB"
Write-Host ">>> Importing the ZXDB SQL dump"
mysql -uroot -p -h 127.0.0.1 -e "source ./ZXDB_mysql.sql"
} else {
Write-Host ">>> FAILED to download the latest ZXdb sql dump"
}
You probably need to provide the correct path to the mysql.exe file and a proper password (-p) if you use one.
Re: SQL for dummies
Ah - I knew it sounded too simple..!
[Edit: I've just got my existing "ZXDB.db" and "ZXDB_mysql.sql" (which I'm happy to download manually) sitting in the same Windows folder]
I don't think I have this... In fact, I've never used GIT. (Don't forget we're dummies here!).
[Edit: I've just got my existing "ZXDB.db" and "ZXDB_mysql.sql" (which I'm happy to download manually) sitting in the same Windows folder]
My Speccy site: thirdharmoniser.com