CODEWITHSUNDEEP
mysqljoinforeign-keysrelational-databasemysql-workbench
Mostafa Amer
Mostafa Amer

Reputation: 312

Joining two foregin keys in the same table references the same primary key

I'm trying to join multiple tables(BooksReview, Followers, Books) with Users table which the Books references userId from Users, BooksReview references userId which is the userId who reviewed a book and references bookId from Books which is the book id that been reviewed on and last but not least the Followers table(and here I think where the problem is) has two references from the same primary key it has follower from userId in Users table and followed also from userId in Users table.

The Problem: I created a MySQL query to fetch the number of books reviews for specific book based on specific user, and to fetch number of books for this user and the number of followers he/she has, but when I added the Followers join part to my query it shows 0 result for all values which the expected values are 4 books, 4 reviews, and 1 follower.

I have tried to change the join types in the query but it endup with the same result and searched joining two foregin keys in the same table for the same primary key but I didn't find anything useful. 

```
 CREATE TABLE IF NOT EXISTS `Authors`.`Users` (
`userId` VARCHAR(100) NOT NULL,
`username` VARCHAR(25) NOT NULL,
`password` VARCHAR(16) NOT NULL,
`email` VARCHAR(254) NOT NULL,
`birthday` DATE NULL,
`aboutMe` TEXT(300) NOT NULL,
`facebookAccount` VARCHAR(25) NULL,
`twitterAccount` VARCHAR(25) NULL,
`linkedinAccount` VARCHAR(25) NULL,
`profileImage` VARCHAR(200) NULL,
PRIMARY KEY (`userId`),
UNIQUE INDEX `username_UNIQUE` (`username` ASC),
UNIQUE INDEX `email_UNIQUE` (`email` ASC))
ENGINE = InnoDB;
```

 CREATE TABLE IF NOT EXISTS `Authors`.`Books` (
`bookId` VARCHAR(100) NOT NULL,
`bookCategory` VARCHAR(25) NOT NULL,
`title` VARCHAR(25) NOT NULL,
`bookCover` VARCHAR(45) NOT NULL,
`bookDescription` VARCHAR(200) NOT NULL,
 `userId` VARCHAR(100) NOT NULL,
`price` DECIMAL(2,2) NOT NULL,
`introduction` VARCHAR(300) NOT NULL,
 PRIMARY KEY (`bookId`),
 INDEX `userId_idx` (`userId` ASC),
 CONSTRAINT `userId`
 FOREIGN KEY (`userId`)
 REFERENCES `Authors`.`Users` (`userId`)
 ON DELETE NO ACTION
 ON UPDATE NO ACTION)
 ENGINE = InnoDB;    
 ```
 ```
  CREATE TABLE IF NOT EXISTS `Authors`.`BooksReview` (
`bookId` VARCHAR(100) NOT NULL,
`rateMessage` VARCHAR(100) NULL,
`rateNumber` DECIMAL(1,1) NULL,
`userId` VARCHAR(100) NOT NULL,
 INDEX `userId_idx` (`userId` ASC),
 CONSTRAINT `bookId`
 FOREIGN KEY (`bookId`)
 REFERENCES `Authors`.`Books` (`bookId`)
 ON DELETE CASCADE
 ON UPDATE CASCADE,
 CONSTRAINT `userId`
 FOREIGN KEY (`userId`)
 REFERENCES `Authors`.`Users` (`userId`)
 ON DELETE CASCADE
 ON UPDATE CASCADE)
 ENGINE = InnoDB;
 ```
CREATE TABLE IF NOT EXISTS `Authors`.`Followers` (
`follower` VARCHAR(100) NOT NULL,
`followed` VARCHAR(100) NOT NULL,
 INDEX `follower_idx` (`follower` ASC),
 INDEX `followed_idx` (`followed` ASC),
 CONSTRAINT `follower`
 FOREIGN KEY (`follower`)
 REFERENCES `Authors`.`Users` (`userId`)
 ON DELETE CASCADE
 ON UPDATE CASCADE,
 CONSTRAINT `followed`
 FOREIGN KEY (`followed`)
 REFERENCES `Authors`.`Users` (`userId`)
 ON DELETE CASCADE
 ON UPDATE CASCADE)
ENGINE = InnoDB;

THIS IS THE QUERY
SELECT count(br.bookId) AS reviewsCount, count(b.bookId) AS booksCount, count(f.follower) AS followersCount
FROM Users AS u
LEFT JOIN Books AS b ON b.userId = u.userId
JOIN Followers AS f ON b.userId = f.followed AND f.follower = u.userId
INNER JOIN BooksReview AS br ON br.bookId = b.bookId 
                    AND b.bookId IN (SELECT bookId 
                                     FROM Books 
                                     WHERE userId = 'dbb21849-ccce-4af1-aa0f-6653919bf956');

I expect the result should be 1 follower, 4 books and 4 reviews but the actual result is 0's for all.

DML:

Users -> 

       userId: dbb21849-ccce-4af1-aa0f-6653919bf956

       username: mostafabbbaron

etc...

Books -> 

       userId: dbb21849-ccce-4af1-aa0f-6653919bf956

       bookId: 5f39c1ae-5e99-4b3a-8ee0-97a80c1ba9b1

etc...

Followers -> 

       follower: dbb21849-ccce-4af1-aa0f-6653919bf956

       folllowed: b39c8e0c-4124-4339-8c30-e1fc8db5f2d4

etc...

BooksReviews -> 

       userId: dbb21849-ccce-4af1-aa0f-6653919bf956

       bookId: aa44a455-dc28-476f-b4b9-47563a717f03

etc...

Upvotes: 0

Views: 62

Answers (1)

Paul Spiegel
Paul Spiegel

Reputation: 31772

Your query is wrong either way. But the reason for getting zeros as result are probably these conditions: b.userId = u.userId and b.userId = f.followed AND f.follower = u.userId.

IF b.userId = u.userId and b.userId = f.followed then f.followed = u.userId

IF f.followed = u.userId and f.follower = u.userId then f.followed = f.follower

That means that the user must follow himself/herself, which I doubt is the case.

I would write the query the following way:

SELECT
count(DISTINCT b.bookId) AS booksCount,
count(br.bookId) AS reviewsCount,
(SELECT COUNT(*) FROM Followers AS f WHERE f.followed = u.userId) AS followersCount
FROM Users AS u
LEFT JOIN Books       AS b  ON b.userId  = u.userId
LEFT JOIN BooksReview AS br ON br.bookId = b.bookId 
WHERE u.userId = 'dbb21849-ccce-4af1-aa0f-6653919bf956'

Note: While it's fine to have Users LEFT JOIN Books LEFT JOIN BooksReview, because you have a "relation chain" Users <- Books <- BooksReview. But you shouldn't just JOIN the Followers table, because it's not really related to Books or BooksReview, and doesn't fit into that chain. That is why I used a subquery in the SELECT clause, to count the followers.

Upvotes: 2

Related Questions