Re: Cross table access query

[Rick James]

> PRIMARY KEY (`hero_id`)
This is an "index" into that table.

> `hero_id` smallint(5) unsigned NOT NULL,
> `hero_id_1` smallint(5) unsigned NOT NULL,

Run this:

SELECT h.hero_id, h.strength,
m.match_id
FROM heroes AS h
JOIN matches AS m ON m.hero_id_1 = h.hero_id
LIMIT 20;

You will see that the two tables are JOINed and the corresponding rows are matched up based on m.hero_id_1 = h.hero_id

It's another discussion to criticize your attempt to have an 'array' of hero_id's in the `matches` table.

As for
> SELECT COUNT(*) FROM matches WHERE [hero_id_1->strength + hero_id_2->strength + hero_id_3->strength] > 10

SELECT COUNT(*)
FROM matches WHERE
( SELECT strength FROM heroes h WHERE h.hero_id = hero_id_1 ) +
( SELECT strength FROM heroes h WHERE h.hero_id = hero_id_2 ) +
( SELECT strength FROM heroes h WHERE h.hero_id = hero_id_3 ) > 10

Or, it could be written this way:

SELECT COUNT(*) FROM matches m
JOIN heroes h1 ON h1.hero_id = m.hero_id_1
JOIN heroes h2 ON h2.hero_id = m.hero_id_1
JOIN heroes h3 ON h3.hero_id = m.hero_id_1
WHERE h1.strength + h2.strength + h3.strength > 10;