SQL for dummies

[R-Tape]

Is anyone willing to write a concise worked example, aimed at non-programmers, of how to query ZXDB using Heidi SQL?

A useful example would be "how many games are there beginning with "J", post 1993, and are MODs?

Does one need to write an .SQL script & run it in Heidi, or can you actually click things in the Heidi package to do it?

[Ralf]

Any particular reason that you would like to use HeidiSQL and not something else as SQL client in your tutorial?

By the way, the difficult moment for non-programmer could be installing the database locally itself.

I went through it, here's my old post about it. I used SqlYog and not Heidi as my tool:
viewtopic.php?f=32&t=328&p=4052&hilit=i ... zxdb#p4044

[R-Tape]

Heidi SQL is what I already have for testing the spreadsheet generated SQL that I then send to Einar.

I'm exposing my ignorance here, but this is my current level: I download the latest ZXDB from the GitHub. I open HeidiSQL and run the SQL file that populates it in Heidi. Have I 'installed it'?

For example, if I click on 'entries' and 'data', I'm able to see all the game entries. I assume there is a way of analysing what I already can see.

I can read and understand the SQL code that inserts new entries into the DB, and occasionally make small modifications. But I can't write my own SQL code. I assumed if a query is done as an SQL statement, if someone posted a written example, I'd be able to modify it for my own needs.

But looking at the thread you link to, I can see that I'm totally out of my depth!

[Ralf]

Looks like somehow you have already a working SQL server, so ignore my post about installing it.

I guess, I could help with some basic SQL queries. Give me some time if nobody else is volunteering.

[R-Tape]

If you had time to demonstrate a simple example it would be great Ralf. Basically if someone could show how to answer this question:

A useful example would be "how many games are there beginning with "J", post 1993, and are MODs?

or even something simpler, that would probably be enough for me to find my own way with it.

[Vampyre]

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.

[Einar Saukas]

Is anyone willing to write a concise worked example, aimed at non-programmers, of how to query ZXDB using Heidi SQL?

All SQL clients will provide the same result, it doesn't matter that you are using Heidi SQL.

A useful example would be "how many games are there beginning with "J", post 1993, and are MODs?

All games:

Code: Select all

SELECT * FROM entries

All games beginning with J:

Code: Select all

SELECT * FROM entries 
WHERE library_title like 'J%'

All games beginning with J (case-insensitive):

Code: Select all

SELECT * FROM entries 
WHERE upper(library_title) like 'J%'

All games beginning with J (case-insensitive), released after 1993:

Code: Select all

SELECT * FROM entries 
WHERE upper(library_title) like 'J%'
AND id in (SELECT entry_id FROM releases WHERE release_year > 1993)

All games beginning with J (case-insensitive), 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_year > 1993)
AND id in (SELECT entry_id FROM relations WHERE relationtype_id = 'm')

[Ralf]

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.

[mention]Einar Saukas[/mention], what do you think about it?

[PeterJ]

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?

Good idea Ralf, but I think [mention]Einar Saukas[/mention] probably has enough on his plate. I'm happy to publish the queries used on the SC website.

[PeterJ]

[mention]R-Tape[/mention]

I would probably start queries looking at one table only, probably entries. Just with select field name serrated by commas, then from entries. Just as described in the past from Einar. Don't run before you can walk.

Then start looking at inner joins, so using more then one table, then finally move onto outer joins.

I'm always happy to help too.

[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);