SQL file - which tables required for a CSSCGC list?
Moderator: druellan
SQL file - which tables required for a CSSCGC list?
Before asking the ZXDB technical question , I have a pre-question :
Is there a way to get a csv file with the list of all Crap games (I want only name/year/creator). I want the the exact results that are produced from the main page https://spectrumcomputing.co.uk/ but written in a text file to process them. I have tried the API (https://api.zxinfo.dk/v3/) but didn't understand how to get only crap games.
If there isn't already a script that does what I want I will make the more technical question:
I installed the ZXDB from https://github.com/zxdb/ZXDB to do a search (I have experience in mysql).
Which tables I must join to get a list of crap-only games;
Thanks in advance.
Btw, Instead of phpmyadmin you could try to setup adminer (https://www.adminer.org/) . It's a single file without the need to install.
Is there a way to get a csv file with the list of all Crap games (I want only name/year/creator). I want the the exact results that are produced from the main page https://spectrumcomputing.co.uk/ but written in a text file to process them. I have tried the API (https://api.zxinfo.dk/v3/) but didn't understand how to get only crap games.
If there isn't already a script that does what I want I will make the more technical question:
I installed the ZXDB from https://github.com/zxdb/ZXDB to do a search (I have experience in mysql).
Which tables I must join to get a list of crap-only games;
Thanks in advance.
Btw, Instead of phpmyadmin you could try to setup adminer (https://www.adminer.org/) . It's a single file without the need to install.
Re: SQL file - which tables required for a CSSCGC list?
I think this works:
Code: Select all
select e.id, e.title
from entries e, contents c
where c.entry_id = e.id
and c.container_id in (select id from entries where title like 'CSSCGC%');
Re: SQL file - which tables required for a CSSCGC list?
Thanks
I haven't run this query but I think this returns the games that have CSSCGC in their title and maybe the compilations of each years competition
I haven't run this query but I think this returns the games that have CSSCGC in their title and maybe the compilations of each years competition
Re: SQL file - which tables required for a CSSCGC list?
I'm a MySQL noob so this may be well off, but I used something like this to filter a crap game list:
Doesn't give you year and author though, someone who knows what they're doing will probably suggest something
Code: Select all
SELECT e.*
FROM entries e
WHERE e.id 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 file - which tables required for a CSSCGC list?
It returns the games that belong to any compilation whose title starts with 'CSSCGC'. I thought that's what you were asking for, but I might have misunderstood.
Re: SQL file - which tables required for a CSSCGC list?
Yes this is what I want. I guess I still haven't undertsood the connection of the tables ( I only uploaded the DB and didn't check the ER diagram).
I will try it
Thanks
Re: SQL file - which tables required for a CSSCGC list?
And this should get you the authors and release years as well.
This will give as many results per title as there are authors associated with it.
Code: Select all
select e.title, l.name, r.release_year
from entries e, contents c, authors a, labels l, releases r
where c.entry_id = e.id
and e.id = a.entry_id
and a.label_id = l.id
and r.entry_id = e.id
and c.container_id in (select id from entries where title like 'CSSCGC%');
- Einar Saukas
- Bugaboo
- Posts: 3196
- Joined: Wed Nov 15, 2017 2:48 pm
Re: SQL file - which tables required for a CSSCGC list?
Code: Select all
select e.title,
group_concat(b.name order by a.author_seq separator ' / ') as authors,
right (t.name,4) as csscgc_year
from entries e
inner join authors a on a.entry_id = e.id
inner join labels b on a.label_id = b.id
inner join members m on m.entry_id = e.id
inner join tags t on m.tag_id = t.id
where t.name like 'CSSCGC%'
group by e.id
Re: SQL file - which tables required for a CSSCGC list?
Do I detect another Firelord Quality Game™ in production @firelord?
Re: SQL file - which tables required for a CSSCGC list?
That looks good, but it doesn't work in SQLite.Einar Saukas wrote: ↑Fri Oct 20, 2023 2:54 pmCode: Select all
select e.title, group_concat(b.name order by a.author_seq separator ' / ') as authors, right (t.name,4) as csscgc_year from entries e inner join authors a on a.entry_id = e.id inner join labels b on a.label_id = b.id inner join members m on m.entry_id = e.id inner join tags t on m.tag_id = t.id where t.name like 'CSSCGC%' group by e.id
- Einar Saukas
- Bugaboo
- Posts: 3196
- Joined: Wed Nov 15, 2017 2:48 pm
Re: SQL file - which tables required for a CSSCGC list?
The OP specified MySQL...
OK, this will work in all databases:
Code: Select all
select e.title,
b1.name as author1, b2.name as author2, b3.name as author3,
right (k.title,4) as csscgc_year
from entries e
inner join contents c on c.entry_id = e.id
inner join entries k on c.container_id = k.id
left join authors a1 on a1.entry_id = e.id and a1.author_seq = 1
left join labels b1 on a1.label_id = b1.id
left join authors a2 on a2.entry_id = e.id and a2.author_seq = 2
left join labels b2 on a2.label_id = b2.id
left join authors a3 on a3.entry_id = e.id and a3.author_seq = 3
left join labels b3 on a3.label_id = b3.id
where k.title like 'CSSCGC%'
Code: Select all
select e.title,
group_concat(b.name order by a.author_seq separator ' / ') as authors,
right (k.title,4) as csscgc_year
from entries e
inner join contents c on c.entry_id = e.id
inner join entries k on c.container_id = k.id
left join authors a on a.entry_id = e.id
left join labels b on a.label_id = b.id
where k.title like 'CSSCGC%'
group by e.id
Re: SQL file - which tables required for a CSSCGC list?
...Fair play, I was thinking I'd probably be wasting my weekend as usual with all the bad weather, but deciding to spend it playing over a thousand crap Speccy games...
Well, good luck...!
Well, good luck...!
My Speccy site: thirdharmoniser.com
- Einar Saukas
- Bugaboo
- Posts: 3196
- Joined: Wed Nov 15, 2017 2:48 pm
Re: SQL file - which tables required for a CSSCGC list?
You are welcome!
Re: SQL file - which tables required for a CSSCGC list?
The results of this query is this :
https://nopaste.net/YgGhl97cgq
2019 is missing (and maybe more years) .If I remember correctly they are already on the ToDo list.