-1

Some books have more than one author, I have a table with with book_id and author_id1, author_id2, author_id3, and author_id4. I have a table with author_id and author_name. How can I join these two tables and the main table with book_id to get the authors names together in a data row from a sql query join.

Example:

SELECT book.book_id, book.title, author.author, book.location
FROM books AS b JOIN book_authors AS ba ON b.book_id = ba.book_id JOIN authors AS a ON REGEX ba.authors_id$ = a.authors_id

Not sure about REGEX ($) use in sql Should display id, title, authors, location How do I get all authors_id# to match authors_id ( notice one has number at end other does not)?

update: So, I would like to get book_authors.authors_id1 to match authors.authors_id, book_authors.authors_id2 to match authors.authors_id, book_authors.authors_id3 to match authors.authors_id, book_authors.authors_id4 to match authors.authors_id and return all the matching authors in list.

Harold Meneley
  • 45
  • 7
  • Does this answer your question? [Mysql get values from all rows but in one record by comma separated](https://stackoverflow.com/questions/37833970/mysql-get-values-from-all-rows-but-in-one-record-by-comma-separated) – devlin carnate Aug 10 '22 at 21:18
  • Constraints (including PKs & FKs) need not hold, be known or be declared to record or query. In the relational (& ER) model a table represents a relation/relationship/association & that is how we record & query. Constraint declarations are for integrity--they allow a DBMS to reject attempted updates to known invalid states. PS [How do I ask and answer homework questions?](https://meta.stackoverflow.com/q/334822/3404097) [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) [ask] [Help] [mre] [edit] – philipxy Aug 10 '22 at 21:28
  • Debug questions require a [mre]--cut & paste & runnable code including initialization; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For SQL include DDL & tabular initialization code. For debug that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. [ask] [Help] When you get a result you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. (Debugging fundamental.) – philipxy Aug 11 '22 at 17:17
  • This is a simple sql query asking how or if I can use regex in select join statement – Harold Meneley Sep 05 '22 at 16:54

1 Answers1

0
...
    # merge book_authors and authors into one dataframe
    ba_df.rename(columns= {'authors_id1': 'authors_id'}, inplace=True)
    ba_df['authors_id'] = ba_df['authors_id'].map(a_df.set_index('authors_id')['authors_name'])
    ba_df.rename(columns = {'authors_id':'authors_name1', 'authors_id2': 'authors_id'}, inplace = True)
    ba_df['authors_id'] = ba_df['authors_id'].map(a_df.set_index('authors_id')['authors_name'])
    ba_df.rename(columns = {'authors_id':'authors_name2', 'authors_id3': 'authors_id'}, inplace = True)
    ba_df['authors_id'] = ba_df['authors_id'].map(a_df.set_index('authors_id')['authors_name'])
    ba_df.rename(columns = {'authors_id':'authors_name3', 'authors_id4': 'authors_id'}, inplace = True)
    ba_df['authors_id'] = ba_df['authors_id'].map(a_df.set_index('authors_id')['authors_name'])
    ba_df.rename(columns = {'authors_id':'authors_name4'}, inplace = True)
...

Was working through another dataframe and got the idea to use map after rename to set_index the same on both dataframes. Now, the map lines can work, just have to rename the common column , so as not to overwrite, in this case it was authors_id, replaced with authors_name1, 2, 3 & 4, which equates to the authors_id1, 2, 3 & 4. And yes, it is not pure sql, but it works for python, which is where I had the problem.

Harold Meneley
  • 45
  • 7