Member Avatar for AngelicOne

I have a book table and a author table which contains book id for the book tbl and author id for the author tbl.

Then I create another table which is Book_Author and it must have the book id and author id join together. How to do this?

  • 6 Contributors
  • 8 Replies
  • 120 Views
  • 4 Days Discussion Span
  • Latest Post Latest Post by AngelicOne

Dani AI

Generated

A proper many-to-many between books and authors uses a junction table that stores the two IDs and enforces referential integrity. Avoid concatenating IDs into a single column (as suggested earlier) because you lose foreign‑key checks, efficient indexing, and clear uniqueness. Define the junction table with a composite primary key (or a unique index) and two foreign keys so duplicates and orphan links are prevented.

A recommended table definition (example) is:

CREATE TABLE Book_Author (
  Book_ID INT NOT NULL,
  Author_ID INT NOT NULL,
  PRIMARY KEY (Book_ID, Author_ID),
  FOREIGN KEY (Book_ID) REFERENCES Book(Book_ID),
  FOREIGN KEY (Author_ID) REFERENCES Author(Author_ID)
);

To insert a relationship when you only have a book title and an author name, select the two IDs from their tables and insert that pair — but do not reference the junction table in the SELECT (that creates the chicken‑and‑egg issue warned about). A safe pattern that also avoids duplicates is:

INSERT INTO Book_Author (Book_ID, Author_ID)
SELECT b.Book_ID, a.Author_ID
FROM Book b
CROSS JOIN Author a
WHERE b.Book_Title = 'little' AND a.Author_Name = 'Mark'
  AND NOT EXISTS (
    SELECT 1 FROM Book_Author ba
    WHERE ba.Book_ID = b.Book_ID AND ba.Author_ID = a.Author_ID
  );

If an INSERT affects zero rows (like saw), debug with these steps: run the SELECT part alone to confirm it returns rows; ensure you are not joining through Book_Author; check for foreign key errors or type mismatches; confirm column names are correct and you are not using qualified names in the INSERT column list; and watch for uniqueness constraints blocking the insert. For MySQL specifics on table creation and INSERT ... SELECT behavior, see the official docs: CREATE TABLE foreign keys and INSERT ... SELECT.

Recommended Answers

All 8 Replies

Member Avatar for Mitja Bonca

An example in console application how to join data tables:
One question: Do you have in both tables the same number of Ids?
If now, this code can give you an error.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace Okt17JoinTables
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable tableBooks = new DataTable("books");
            DataTable tableAuthors = new DataTable("authors");
            DataTable tableJoin = new DataTable("books&authors");
            DataColumn column;
            DataRow row;

            tableBooks.Columns.Add(new DataColumn("BookID", typeof(int)));
            tableAuthors.Columns.Add(new DataColumn("AuthorID", typeof(int)));
            
            //POPULATING TABLES WITH SOME VALUES:
            int[] booksID = new int[] { 5, 4, 6, 2, 9 };
            int[] authorsID = new int[] { 23, 12, 13, 24, 16 };

            for (int i = 0; i < 5; i++)
            {
                row = tableBooks.NewRow();
                row["BookID"] = booksID[i];
                tableBooks.Rows.Add(row);

                row = tableAuthors.NewRow();
                row["AuthorID"] = authorsID[i];
                tableAuthors.Rows.Add(row);
            }

            tableJoin.Columns.Add(new DataColumn("BookID", typeof(int)));
            tableJoin.Columns.Add(new DataColumn("AuthorID", typeof(int)));

            foreach (DataRow dr in tableBooks.Rows)
            {
                row = tableJoin.NewRow();
                row["BookID"] = dr[0];
                tableJoin.Rows.Add(row);
            }
            int counter = 0;
            foreach (DataRow dr in tableAuthors.Rows)
            {
                row = tableJoin.Rows[counter];
                row["AuthorID"] = dr[0];
                counter++;
            }
        }
    }
}
Member Avatar for AngelicOne

This must be done in SQL

Member Avatar for jeoremil

I have the same questiooooon!

Member Avatar for rohand

You can do in below way :

Create table Book_Author(joincolum As bookid+authorid)

I have a book table and a author table which contains book id for the book tbl and author id for the author tbl.

Then I create another table which is Book_Author and it must have the book id and author id join together. How to do this?

Member Avatar for pakunoda

I made this

nsert into Book_Author (Book.Book_ID, Author.Author_ID) select b.Book_ID, a.Author_ID from Book AS b INNER JOIN Book_Author AS ba ON ba.Book_ID = b.Book_ID inner join Author AS a ON a.Author_ID = ba.Author_ID where b.Book_Title = 'little' AND a.Author_Name = 'Mark'
Insert into Book_Author (Book.Book_ID, Author.Author_ID) select b.Book_ID, a.Author_ID from Book AS b INNER JOIN Book_Author AS ba ON ba.Book_ID = b.Book_ID inner join Author AS a ON a.Author_ID = ba.Author_ID where b.Book_Title = 'little' AND a.Author_Name = 'Mark'


But it doesn't affect any rows.

Could someone help me with this?

I have three tables as I mention above.

Member Avatar for hyperion

Problem lies in the select statment:

SELECT b.Book_ID, a.Author_ID
  FROM Book AS b
 INNER JOIN Book_Author AS ba ON ba.Book_ID = b.Book_ID  -- HERE IS THE ERROR [1]
 INNER JOIN Author AS a ON a.Author_ID = ba.Author_ID
 WHERE b.Book_Title = 'little'
   AND a.Author_Name = 'Mark'

[1] Here we expect some value to be present in Book_Author table and in turn we want to insert the obtained values in Book_Author table again. This is more like chicken and egg problem.

For a situation like this you will be needing a simpler Insert script like Insert into Book_Author (BookId, AuthorId) Values (1, 2) -- SEE NOTE [2] [2]Here values used in Values clause are BookId from Books table and Author Id in Authors table respectively.


There is another problem with Insert Statment. Insert into Book_Author (Book.Book_ID, Author.Author_ID) For correct Insert statment please visit this site.

Member Avatar for AngelicOne

The author ID that will be insert to Book_Author whi chwill come from different table.

Edited by AngelicOne because: n/a
Member Avatar for AngelicOne

anyone?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.