join two identity columns

[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?

[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++;
            }
        }
    }
}

[AngelicOne]

This must be done in SQL

[jeoremil]

I have the same questiooooon!

[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?

[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.

[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.

[AngelicOne]

@hyperion

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

[AngelicOne]

anyone?