How to select records Composite Primary key and Foreign key reference (many to many relationship)

Viewed 43

I am tyring to learn composite Primary Key and Foregin Key. Wherein I am trying to have many to many mappings. Have three tables here.

CREATE  TABLE  authors(
  authorID INT PRIMARY  KEY ,
  firstName VARCHAR(20),
  lastName VARCHAR(20),
  gender VARCHAR(20)
);


CREATE  TABLE  books(
  bookId INT PRIMARY  KEY ,
  bookTitle VARCHAR(20),
  price INT,
  publisher VARCHAR(20)
);


/*Junction table*/
CREATE  table authors_Books(
  authorId INT NOT NULL,
  bookId INT NOT NULL ,
  FOREIGN KEY (authorID)
    REFERENCES authors(authorID),
  FOREIGN KEY (bookId)
    REFERENCES books(bookId)
 );

The records inserted are

Authors Table:

insert into authors values (1,'Mark','Dunn', 'Male');
insert into authors values (2,'Sara','Longhorn', 'Female');
insert into authors values (3,'Jessica','Dale', 'Female');
insert into authors values (4,'Steve','Wicht', 'Male');

Books Table

insert into books values(1,'Learn SQL', 10,1);
insert into books values(2,'Learn C#', 20,1);
insert into books values(3,'Learn CSS', 15,1);
insert into books values(4,'Learn HTML', 20,0);

Authors books Table

insert into authors_Books values(1,1);
insert into authors_Books values(1,2);
insert into authors_Books values(2,1);

We can observe that the author Mark Dunn has written two books (Lean SQL and Learn C#). Also, the book Learn SQL is written by two authors Mark Dunn and Sara Longhorn. How do I write an SQL query to get the books written by Mark? I assume that we can get results without using joins right?

I have tried this query but it's not correct

select 
  books.bookTitle, 
  books.price, 
  books.publisher, 
  authors.firstName, 
  authors.lastName 
from 
  books, 
  authors, 
  authors_Books 
where 
  authors_Books.authorId=1 ;
1 Answers

you can write query without join but need condition in "Where", like that:

select 
  books.bookTitle, 
  books.price, 
  books.publisher, 
  authors.firstName, 
  authors.lastName 
from 
  books, 
  authors, 
  authors_Books 
where 
  authors_Books.authorId=1 
  and authors.authorID = authors_Books.authorId
  and authors_Books.bookId = books.bookId
Related