Thread: Many-to-many

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2015
    Posts
    8
    Rep Power
    0

    Many-to-many


    One books can many authors, one author can many books. I need BOOKS_AUTHORS additional table?
    I have
    book1 - auth1
    book2 - auth2,auth3
    book3 - auth1,auth3
    book4 - auth1,auth4
    book5 - auth2,auth3,auth4

    BOOKS has 5 items, AUTHORS 4 items and BOOKS_AUTHORS? How create BOOKS_AUTHORS table ?
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2015
    Posts
    8
    Rep Power
    0
    BOOKS
    1,book1
    2,book2
    3,book3
    4,book4
    5,book5

    AUTHORS
    1,auth1
    2,auth2
    3,auth3
    4,auth4

    BOOKS_AUTHORS
    1,1
    2,2
    2,3
    3,1
    3,3
    4,1
    4,4
    5,2
    5,3
    5,4

    How fill table BOOKS_AUTHORS with DevArt components and how display all authors for book?
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,803
    Rep Power
    353
    Originally Posted by Borneq
    How fill table BOOKS_AUTHORS with DevArt components
    I have no idea what these DevArt components are (and why you would want to put "components" into a table that is about books), but you "fill" a table by running the appropriate INSERT statements, e.g.:
    Code:
    insert into books_authors (book_id, author_id) values (1,1);
    insert into books_authors (book_id, author_id) values (2,2);
    insert into books_authors (book_id, author_id) values (2,3);
    insert into books_authors (book_id, author_id) values (3,1);
    insert into books_authors (book_id, author_id) values (3,3);
    insert into books_authors (book_id, author_id) values (4,1);
    insert into books_authors (book_id, author_id) values (4,4);
    insert into books_authors (book_id, author_id) values (5,2);
    insert into books_authors (book_id, author_id) values (5,3);
    insert into books_authors (book_id, author_id) values (5,4);
    and how display all authors for book?
    Code:
    select a.*
    from authors a
      join books_authors ba on a.id = ba.author_id 
    where ba.book_id = 1;
    or if you prefer to specify the book name instead of the ID, you need to join to the books table as well:
    Code:
    select a.*
    from authors a
      join books_authors ba on a.id = ba.author_id 
      join books b on b.id = ba.book_id 
    where b.name = 'book2';

    Comments on this post

    • Will-O-The-Wisp agrees : Thanks for helping out here!
    Last edited by shammat; August 16th, 2015 at 07:53 AM.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2008
    Posts
    74
    Rep Power
    11
    see how to use Table "CTE" to creare one tree dependence

    book Helen Borie or search in google about use CTE "Common Table Expression" in firebird sql
    Last edited by emailx45; August 30th, 2015 at 06:20 PM.

IMN logo majestic logo threadwatch logo seochat tools logo