SQL file - which tables required for a CSSCGC list?

[firelord]

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.

[SkoolKid]

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%');

[firelord]

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

[Morkin]

I'm a MySQL noob so this may be well off, but I used something like this to filter a crap game list:

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%'
       );

Doesn't give you year and author though, someone who knows what they're doing will probably suggest something

[SkoolKid]

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

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.

[firelord]

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.

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

[SkoolKid]

And this should get you the authors and release years as well.

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%');

This will give as many results per title as there are authors associated with it.

[Einar Saukas]

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

[R-Tape]

Do I detect another Firelord Quality Game™ in production @firelord?

[SkoolKid]

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

That looks good, but it doesn't work in SQLite.

[R-Tape]

Works in HeidiSQL here.

[Einar Saukas]

That looks good, but it doesn't work in SQLite.

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%'

I just realized my previous query doesn't list titles without known authors. This new version for MySQL will get them too:

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

[Morkin]

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

[firelord]

That looks good, but it doesn't work in SQLite.

Thanks this worked (mysql)

[Einar Saukas]

You are welcome!

[firelord]

That looks good, but it doesn't work in SQLite.

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.