0

I need assistance creating a query for two tables.

  1. The first table books already has an author (the author's name) and illustrator (illustrator) in the author and illustrator column.

  2. The second table people also has the name of the author in the name column.

  3. The third table books_people has the two columns book_id and author_id and a third column called occupation

This is more of a conversion from having the authors directly in the books table to having a separate table creating a relation between two tables (one-to-many) ( table: books_authors ). I would also need help making the column of that relation submit the occupation.

Books Table:

+----+--------------+----------------+-----------------+
| id |    title     |     author     |     artist      |
+----+--------------+----------------+-----------------+
|  1 | Tower of God | SIU            | SIU             |
|  2 | Zippy Ziggy  | KIM Eun-jung   | HWANG Seung-man |
|  4 | Beelzebub    | Tamura Ryuuhei | Tamura Ryuuhei  |
+----+--------------+----------------+-----------------+

People Table:

+-------+-----------------+
|  id   |      name       |
+-------+-----------------+
|    32 | SIU             |
|  4053 | KIM Eun-jung    |
|  4055 | HWANG Seung-man |
| 28490 | Tamura Ryuuhei  |
+-------+-----------------+

Books_People Table

+----------+-----------+----------------+
| comic_id | person_id |   occupation   |
+----------+-----------+----------------+

Expected result:

+----------+-----------+----------------+
| comic_id | person_id |   occupation   |
+----------+-----------+----------------+
|        1 |        32 | author, artist |
|        2 |      4053 | author         |
|        2 |      4055 | artist         |
|        4 |     28490 | author, artist |
+----------+-----------+----------------+

What im trying to do here is "create" the rows after the relations have been made.

Terrabyte
  • 342
  • 4
  • 15
  • give sample data and sample output – Sas Mar 31 '18 at 07:02
  • If the `books` table references authors and illustrators as `people` by name, then you need to fix that first. – Aluan Haddad Mar 31 '18 at 07:05
  • provide a proper data sample and the expected result – ScaisEdge Mar 31 '18 at 07:28
  • @AluanHaddad it doesnt have the reference. The books table only has the name of the author ( no other columns, other than the id). – Terrabyte Mar 31 '18 at 07:31
  • What exactly should the query you are looking for be doing? – Namoshek Mar 31 '18 at 07:32
  • @Terrabyte then how can you determine who the author of any book is? You should store the person's Id instead. In other words, I'm saying it should be a reference. – Aluan Haddad Mar 31 '18 at 07:33
  • i have provided some data structure – Terrabyte Mar 31 '18 at 07:56
  • For the expected results there is no need to join anything, all fields are in the Books_People table. All you need is group_concat() with a group by clause to produce the expected outcome. – Shadow Mar 31 '18 at 08:09
  • @Shadow what would be the resulting query to do this? – Terrabyte Mar 31 '18 at 08:15
  • See the duplicate topic for the solution. – Shadow Mar 31 '18 at 08:19
  • anyone got a hint on how to do it for my query? – Terrabyte Mar 31 '18 at 09:40
  • Select b.id, pe.id person_id , pe.name, case when b.author= b.artist then cast('author,artist' as nvarchar2(200)) end|| case when (b.author<>b.artist and b.author=pe.name) then cast( 'author' as nvarchar2(200)) end|| case when (b.author<>b.artist and b.artist=pe.name) then cast( 'artist' as nvarchar2(200)) end occupation from books b join people pe on b.author like pe.name --union second sql – SevincQurbanova Mar 31 '18 at 16:48
  • union select b.id, pe.id person_id , pe.name , case when b.author= b.artist then cast('author,artist' as nvarchar2(200)) end|| case when (b.author<>b.artist and b.author=pe.name) then cast( 'author' as nvarchar2(200)) end|| case when (b.author<>b.artist and b.artist=pe.name) then cast( 'artist' as nvarchar2(200)) end occupation from books b join people pe on b.artist like pe.name where b.author not like b.artist – SevincQurbanova Mar 31 '18 at 16:50
  • this works in oracle sql – SevincQurbanova Mar 31 '18 at 16:50
  • @SevincQurbanovado Are you able to provide a mysql equivalent please? – Terrabyte Mar 31 '18 at 19:34
  • nvm i figured it out, but how do i create rows after i've gone ahead and set the groupings – Terrabyte Mar 31 '18 at 19:59

0 Answers0