SELECT Primary key that refers to Foreign key

How do I write a query that compares a primary key to a foreign key to see if they refer to the same record? I was thinking something like:

SELECT BookID from books WHERE BookID = Publisher, but obviously that’s not working since they’re different numbers.

Example:

CREATE TABLE IF NOT EXISTS `books` (
  `BookID` int(9) NOT NULL AUTO_INCREMENT,
  `Title` varchar(1000) NOT NULL,
  `Pages` int(10) NOT NULL,
  `Description` TEXT NOT NULL,
  `Publisher` varchar(1000) NOT NULL,,
  PRIMARY KEY (`BookID`),
  FOREIGN KEY (`Publisher`) REFERENCES `publisher_id`(`ID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Answer

FOREIGN KEY (`Publisher`) REFERENCES `publisher_id`(`ID`) ON DELETE CASCADE

This line in your code means that there is another table called publisher_id in this database having a column named ID. Is that correct?

In normal scenarios, you name your table for eg: Publisher. Let ID be the primary key column of the Publisher table. It can contain more columns such as name, address etc.

Assuming one book has one publisher and one publisher can publish many books, In the books table have a column called publisher_id. Then you can reference it as a foreign key as follows (have it in the create statement just like you have done above).

FOREIGN KEY (`publisher_id`) REFERENCES `Publisher`(`ID`) ON DELETE CASCADE

If you have a publisher ID with you (for eg: 5) and want to get all the book id(s) published by this publisher, you can run the following query.

SELECT Books.BookID FROM Books WHERE Books.publisher_id = 5;

table_name.column_name is the syntax for MySQL.