Grouping results from multiple tables

Question

I have 6 tables: 3 for general data about anime/manga/ova/whatever and 3 for their genres which is a 1-to-m relationship

anime:                manga:                   ova:

aid | data | ... |    mid | data | ... |       oid | data | ... |
----+------+-----+    ----+------+-----+       ----+------+-----+
  1 | .... | ... |      1 | .... | ... |         1 | .... | ... | 
  2 | .... | ... |      2 | .... | ... |         2 | .... | ... |
  3 | .... | ... |      3 | .... | ... |         3 | .... | ... |
  4 | .... | ... |      4 | .... | ... |         4 | .... | ... |

anime_genre:          manga_genre:             ova_genre:

aid | genre           mid | name               oid | genre
----+--------         ----+----------          ----+--------
  1 | Ecchi             1 | Drama                1 | Action
  1 | Action            2 | Ecchi                2 | Action
  2 | Action            3 | Fighting             3 | Drama
  3 | Drama             4 | Action               4 | Ecchi
  4 | Action

I am trying to get the results grouped whenever someone is searching for a genre to get all the information in one query

Case 1:

result on genre = Action:

  genre | ids                                         
--------+---------------------------------------------
 Action | 1:anime 2:anime 4:anime 4:manga 1:ova 2:ova


result on genre = Ecchi:

  genre | ids
--------+-------------------------------
  Ecchi | 1:anime 4:anime 2:manga 4:ova

Case 2 (preferable):

result on genre = Action:

  genre |   id    | common data | common data | common data
--------+---------+-------------+-------------+-------------
 Action | 1:anime |     ...     |     ...     |     ...
 Action | 2:anime |     ...     |     ...     |     ...
 Action | 4:anime |     ...     |     ...     |     ...
 Action | 4:manga |     ...     |     ...     |     ...
 Action | 1:ova   |     ...     |     ...     |     ...
 Action | 2:ova   |     ...     |     ...     |     ...


result on genre = Ecchi:

 genre |   id    | common data | common data | common data
-------+---------+-------------+-------------+-------------
 Ecchi | 1:anime |     ...     |     ...     |     ...
 Ecchi | 4:anime |     ...     |     ...     |     ...
 Ecchi | 2:manga |     ...     |     ...     |     ...
 Ecchi | 4:ova   |     ...     |     ...     |     ...

I've been bashing my head all day and I could only get the desired result for a single table. I can't manage to get the results for all three in one query.

Any ideas? Can someone point me in the right direction? (The data tables have some columns that are not common but I don't really care if I get null values for those cause I'm parsing the results in js)

See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Mar 04 '18 at 01:00

score 0 · Answer 1 · answered Mar 04 '18 at 01:07

0

Lookup mysql join, you can join tables and search between multi tables for matching answers.

answered Mar 04 '18 at 01:07

Matthew

49
5

score 0 · Answer 2 · answered Mar 04 '18 at 03:34

You can get the list of ids using union all:

select ag.genre, 'anime' as which, ag.aid as id
from anime_genre ag
union all
select mg.genre, 'manga' as which, mg.mid as id
from manga_genre mg 
union all
select og.genre, 'ova' as which, og.oid as id;
from ova_genre og;

You can join in additional columns in the subquery.

Grouping results from multiple tables

2 Answers2