SQL for dummies

[R-Tape]

Thanks fellas. Einar's post worked a treat. It's given me a starting point to experiment with; it all seemed quite impenetrable before.

[PeterJ]

Great stuff, the world is your oyster now.

[Einar Saukas]

All games beginning with J (case-insensitive), originally released after 1993, that are mods:

Code: Select all

SELECT * FROM entries 
WHERE upper(library_title) like 'J%'
AND id in (SELECT entry_id FROM releases WHERE release_seq = 0 AND release_year > 1993)
AND id in (SELECT entry_id FROM relations WHERE relationtype_id = 'm')

[Einar Saukas]

Try this:

SELECT releases.release_year, entries.*
FROM entries INNER JOIN releases ON entries.id = releases.entry_id
WHERE title like 'J%' AND releases.release_year >= 1993 AND entries.is_mod = 1

That'll give you all the records matching. To get the count:

SELECT COUNT(*) as cnt
FROM entries INNER JOIN releases ON entries.id = releases.entry_id
WHERE title like 'J%'
AND releases.release_year >= 1993
AND entries.is_mod = 1

That's specific to MS SQL Server - I think in this case the MySQL syntax will be the same.

Yes, it's the same syntax. Your query works perfectly, except column entries.is_mod doesn't exist anymore in latest ZXDB versions. There's now a new table relations that stores all information about games related to other games. A certain game is considered a "mod" if this game ID is stored in this table, marked as "mod" of another game. Technically it means replacing this:

Code: Select all

SELECT * FROM entries
WHERE is_mod = 1

with this:

Code: Select all

SELECT * FROM entries
WHERE id in (SELECT entry_id FROM relations WHERE relationtype_id = 'm')

This change allows Ku-Ku to be stored as "mod" of 2 different games, for instance.

[Einar Saukas]

Actually I believe ZXDB would benefit from placing there some basic sql views showing in practice joins between tables and eliminating the need to write more complex queries each time.

@Einar Saukas, what do you think about it?

Sure! Here's my first attempt:

Code: Select all

SELECT e.id, e.title, e.library_title, e.is_xrated, e.is_crap, mt.text AS machinetype,
e.max_players, gt.text AS genretype, st.text AS spot_genretype, pt.text AS publicationtype,
vt.text AS availabletype, e.known_errors, e.comments, e.spot_comments, e.hardware_blurb,
e.without_load_screen, e.without_inlay, e.hide_from_stp, i.text AS idiom, e.mag_ratings,
e.issue_id, e.book_isbn, e.book_pages,
GROUP_CONCAT(IF(a.team_id IS NULL,la.name,CONCAT(la.name,' [',lt.name,']')) ORDER BY a.author_seq SEPARATOR ', ') AS authors,
r0.release_year AS original_release_year, r0.release_month AS original_release_month,
r0.release_day AS original_release_day, r0.budget_price AS original_budget_price,
r0.microdrive_price AS original_microdrive_price, r0.disk_price AS original_disk_price,
r0.cartridge_price AS original_cartridge_price,
GROUP_CONCAT(l0.name ORDER BY p0.publisher_seq SEPARATOR ', ') AS original_publishers,
GROUP_CONCAT(CONCAT(ea.title,' (',ea.id,')') ORDER BY ea.library_title SEPARATOR ', ') AS authored_with,
GROUP_CONCAT(CONCAT(ed.title,' (',ed.id,')') ORDER BY ed.library_title SEPARATOR ', ') AS pack_depending_on,
GROUP_CONCAT(CONCAT(ee.title,' (',ee.id,')') ORDER BY ee.library_title SEPARATOR ', ') AS editor_of,
GROUP_CONCAT(CONCAT(ei.title,' (',ei.id,')') ORDER BY ei.library_title SEPARATOR ', ') AS inspired_by,
GROUP_CONCAT(CONCAT(em.title,' (',em.id,')') ORDER BY em.library_title SEPARATOR ', ') AS mod_from,
GROUP_CONCAT(CONCAT(ep.title,' (',ep.id,')') ORDER BY ep.library_title SEPARATOR ', ') AS another_platform_for,
GROUP_CONCAT(CONCAT(er.title,' (',er.id,')') ORDER BY er.library_title SEPARATOR ', ') AS runs_with,
GROUP_CONCAT(CONCAT(eu.title,' (',eu.id,')') ORDER BY eu.library_title SEPARATOR ', ') AS upgraded_from
FROM entries e
INNER JOIN releases r0 ON r0.entry_id = e.id AND r0.release_seq = 0
LEFT JOIN authors a ON a.entry_id = e.id
LEFT JOIN labels la ON la.id = a.label_id
LEFT JOIN labels lt ON lt.id = a.team_id
LEFT JOIN publishers p0 ON p0.entry_id = r0.entry_id AND p0.release_seq = r0.release_seq
LEFT JOIN labels l0 ON l0.id = p0.label_id
LEFT JOIN machinetypes mt ON e.machinetype_id = mt.id
LEFT JOIN genretypes gt ON e.genretype_id = gt.id
LEFT JOIN genretypes st ON e.spot_genretype_id = st.id
LEFT JOIN publicationtypes pt ON e.publicationtype_id = pt.id
LEFT JOIN availabletypes vt ON e.availabletype_id = vt.id
LEFT JOIN idioms i ON i.id = e.idiom_id
LEFT JOIN relations ra ON ra.entry_id = e.id AND ra.relationtype_id = 'a'
LEFT JOIN entries ea ON ra.original_id = ea.id
LEFT JOIN relations rd ON rd.entry_id = e.id AND rd.relationtype_id = 'd'
LEFT JOIN entries ed ON rd.original_id = ed.id
LEFT JOIN relations re ON re.entry_id = e.id AND re.relationtype_id = 'e'
LEFT JOIN entries ee ON re.original_id = ee.id
LEFT JOIN relations ri ON ri.entry_id = e.id AND ri.relationtype_id = 'i'
LEFT JOIN entries ei ON ri.original_id = ei.id
LEFT JOIN relations rm ON rm.entry_id = e.id AND rm.relationtype_id = 'm'
LEFT JOIN entries em ON rm.original_id = em.id
LEFT JOIN relations rp ON rp.entry_id = e.id AND rp.relationtype_id = 'p'
LEFT JOIN entries ep ON rp.original_id = ep.id
LEFT JOIN relations rr ON rr.entry_id = e.id AND rr.relationtype_id = 'r'
LEFT JOIN entries er ON rr.original_id = er.id
LEFT JOIN relations ru ON ru.entry_id = e.id AND ru.relationtype_id = 'u'
LEFT JOIN entries eu ON ru.original_id = eu.id
GROUP BY e.id

I will improve it later

[Einar Saukas]

Ops! My first attempt had a bug that duplicated some data. Here's my second attempt:

Code: Select all

CREATE VIEW z_products AS
SELECT e.id, e.title, e.library_title, e.is_xrated, e.is_crap, mt.text AS machinetype,
e.max_players, gt.text AS genretype, st.text AS spot_genretype, pt.text AS publicationtype,
vt.text AS availabletype, e.known_errors, e.comments, e.spot_comments, e.hardware_blurb,
e.without_load_screen, e.without_inlay, e.hide_from_stp, i.text AS idiom, e.mag_ratings,
e.issue_id, e.book_isbn, e.book_pages,
GROUP_CONCAT(DISTINCT CONCAT(la.name,IF(a.team_id IS NULL,'',CONCAT(' [',lt.name,']')),IF(la.id in (select label_id from roles where entry_id = e.id),
(SELECT CONCAT(' (',GROUP_CONCAT(tx.text ORDER BY tx.text SEPARATOR ', '),')') FROM roles rx INNER JOIN roletypes tx ON rx.roletype_id = tx.id
WHERE rx.entry_id = a.entry_id and rx.label_id = a.label_id GROUP BY rx.entry_id, rx.label_id),'')) ORDER BY a.author_seq SEPARATOR ', ') AS authors,
r0.release_year AS original_release_year, r0.release_month AS original_release_month,
r0.release_day AS original_release_day, r0.budget_price AS original_budget_price,
r0.microdrive_price AS original_microdrive_price, r0.disk_price AS original_disk_price,
r0.cartridge_price AS original_cartridge_price,
GROUP_CONCAT(DISTINCT l0.name ORDER BY p0.publisher_seq SEPARATOR ', ') AS original_publishers,
GROUP_CONCAT(DISTINCT concat(sa.title,' (',si.text,')') ORDER BY sa.library_title SEPARATOR ', ') AS original_aliases,
GROUP_CONCAT(DISTINCT CONCAT(ea.title,' (',ea.id,')') ORDER BY ea.library_title SEPARATOR ', ') AS authored_with,
GROUP_CONCAT(DISTINCT CONCAT(ed.title,' (',ed.id,')') ORDER BY ed.library_title SEPARATOR ', ') AS pack_depending_on,
GROUP_CONCAT(DISTINCT CONCAT(ee.title,' (',ee.id,')') ORDER BY ee.library_title SEPARATOR ', ') AS editor_of,
GROUP_CONCAT(DISTINCT CONCAT(ei.title,' (',ei.id,')') ORDER BY ei.library_title SEPARATOR ', ') AS inspired_by,
GROUP_CONCAT(DISTINCT CONCAT(em.title,' (',em.id,')') ORDER BY em.library_title SEPARATOR ', ') AS mod_from,
GROUP_CONCAT(DISTINCT CONCAT(ep.title,' (',ep.id,')') ORDER BY ep.library_title SEPARATOR ', ') AS another_platform_for,
GROUP_CONCAT(DISTINCT CONCAT(er.title,' (',er.id,')') ORDER BY er.library_title SEPARATOR ', ') AS runs_with,
GROUP_CONCAT(DISTINCT CONCAT(eu.title,' (',eu.id,')') ORDER BY eu.library_title SEPARATOR ', ') AS upgraded_from,
GROUP_CONCAT(DISTINCT xc.name ORDER BY xc.name SEPARATOR ', ') AS competitions,
GROUP_CONCAT(DISTINCT xf.name ORDER BY xf.name SEPARATOR ', ') AS features,
GROUP_CONCAT(DISTINCT xj.name ORDER BY xj.name SEPARATOR ', ') AS control_options,
GROUP_CONCAT(DISTINCT xl.name ORDER BY xl.name SEPARATOR ', ') AS prog_languages,
GROUP_CONCAT(DISTINCT xn.name ORDER BY xn.name SEPARATOR ', ') AS turn_modes,
GROUP_CONCAT(DISTINCT CONCAT(xs.name,' (#',xs.series_seq,')') ORDER BY xs.name SEPARATOR ', ') AS series,
GROUP_CONCAT(DISTINCT xt.name ORDER BY xt.name SEPARATOR ', ') AS themes,
GROUP_CONCAT(DISTINCT xu.name ORDER BY xu.name SEPARATOR ', ') AS unsorted_sets,
GROUP_CONCAT(DISTINCT xy.name ORDER BY xy.name SEPARATOR ', ') AS multiplayer_modes
FROM entries e
INNER JOIN releases r0 ON r0.entry_id = e.id AND r0.release_seq = 0
LEFT JOIN authors a ON a.entry_id = e.id
LEFT JOIN labels la ON la.id = a.label_id
LEFT JOIN labels lt ON lt.id = a.team_id
LEFT JOIN publishers p0 ON p0.entry_id = r0.entry_id AND p0.release_seq = r0.release_seq
LEFT JOIN labels l0 ON l0.id = p0.label_id
LEFT JOIN machinetypes mt ON e.machinetype_id = mt.id
LEFT JOIN genretypes gt ON e.genretype_id = gt.id
LEFT JOIN genretypes st ON e.spot_genretype_id = st.id
LEFT JOIN publicationtypes pt ON e.publicationtype_id = pt.id
LEFT JOIN availabletypes vt ON e.availabletype_id = vt.id
LEFT JOIN idioms i ON i.id = e.idiom_id
LEFT JOIN relations ra ON ra.entry_id = e.id AND ra.relationtype_id = 'a'
LEFT JOIN entries ea ON ra.original_id = ea.id
LEFT JOIN relations rd ON rd.entry_id = e.id AND rd.relationtype_id = 'd'
LEFT JOIN entries ed ON rd.original_id = ed.id
LEFT JOIN relations re ON re.entry_id = e.id AND re.relationtype_id = 'e'
LEFT JOIN entries ee ON re.original_id = ee.id
LEFT JOIN relations ri ON ri.entry_id = e.id AND ri.relationtype_id = 'i'
LEFT JOIN entries ei ON ri.original_id = ei.id
LEFT JOIN relations rm ON rm.entry_id = e.id AND rm.relationtype_id = 'm'
LEFT JOIN entries em ON rm.original_id = em.id
LEFT JOIN relations rp ON rp.entry_id = e.id AND rp.relationtype_id = 'p'
LEFT JOIN entries ep ON rp.original_id = ep.id
LEFT JOIN relations rr ON rr.entry_id = e.id AND rr.relationtype_id = 'r'
LEFT JOIN entries er ON rr.original_id = er.id
LEFT JOIN relations ru ON ru.entry_id = e.id AND ru.relationtype_id = 'u'
LEFT JOIN entries eu ON ru.original_id = eu.id
LEFT JOIN aliases sa ON sa.entry_id = e.id AND sa.release_seq = 0
LEFT JOIN idioms si ON si.id = sa.idiom_id
LEFT JOIN (SELECT g.name, m.entry_id FROM members m INNER JOIN groups g ON m.group_id = g.id AND g.grouptype_id = 'C') AS xc ON xc.entry_id = e.id
LEFT JOIN (SELECT g.name, m.entry_id FROM members m INNER JOIN groups g ON m.group_id = g.id AND g.grouptype_id = 'F') AS xf ON xf.entry_id = e.id
LEFT JOIN (SELECT g.name, m.entry_id FROM members m INNER JOIN groups g ON m.group_id = g.id AND g.grouptype_id = 'J') AS xj ON xj.entry_id = e.id
LEFT JOIN (SELECT g.name, m.entry_id FROM members m INNER JOIN groups g ON m.group_id = g.id AND g.grouptype_id = 'L') AS xl ON xl.entry_id = e.id
LEFT JOIN (SELECT g.name, m.entry_id FROM members m INNER JOIN groups g ON m.group_id = g.id AND g.grouptype_id = 'N') AS xn ON xn.entry_id = e.id
LEFT JOIN (SELECT g.name, m.entry_id, m.series_seq FROM members m INNER JOIN groups g ON m.group_id = g.id AND g.grouptype_id = 'S') AS xs ON xs.entry_id = e.id
LEFT JOIN (SELECT g.name, m.entry_id FROM members m INNER JOIN groups g ON m.group_id = g.id AND g.grouptype_id = 'T') AS xt ON xt.entry_id = e.id
LEFT JOIN (SELECT g.name, m.entry_id FROM members m INNER JOIN groups g ON m.group_id = g.id AND g.grouptype_id = 'U') AS xu ON xu.entry_id = e.id
LEFT JOIN (SELECT g.name, m.entry_id FROM members m INNER JOIN groups g ON m.group_id = g.id AND g.grouptype_id = 'Y') AS xy ON xy.entry_id = e.id
GROUP BY e.id

[Vampyre]

Yes, it's the same syntax. Your query works perfectly, except column entries.is_mod doesn't exist anymore in latest ZXDB versions. There's now a new table relations that stores all information about games related to other games. A certain game is considered a "mod" if this game ID is stored in this table, marked as "mod" of another game. Technically it means replacing this:

Code: Select all

SELECT * FROM entries
WHERE is_mod = 1

with this:

Code: Select all

SELECT * FROM entries
WHERE id in (SELECT entry_id FROM relations WHERE relationtype_id = 'm')

This change allows Ku-Ku to be stored as "mod" of 2 different games, for instance.

Good to know, thanks. It's about time I upgraded my local ZXDB to a more recent version!

[R-Tape]

Does anyone know why HeidiSQL doesn't display all information for long lists?

In this case I want to see the max id in magrefs, and when I click 'show all' (which works for other tables), I can only see as far as 120,732. When I query with "SELECT MAX(id) FROM magrefs;", it reports the true max of 222,344.

Just a quirk of the software?

[PeterJ]

Hi [mention]R-Tape[/mention],

Is there anything in Preferences > Text Formatting > Number of rows displayed in data tab ?

[Einar Saukas]

Does anyone know why HeidiSQL doesn't display all information for long lists?

In this case I want to see the max id in magrefs, and when I click 'show all' (which works for other tables), I can only see as far as 120,732. When I query with "SELECT MAX(id) FROM magrefs;", it reports the true max of 222,344.

Just a quirk of the software?

The log window at the bottom should have a message like this:

Code: Select all

/* Browsing is currently limited to a maximum of 100,000 rows. To see more rows, increase this maximum in Tools > Preferences > Data. */

Although it's actually "Tools > Preferences > Text formatting".

[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