CODEWITHSUNDEEP
phpmysqlsql
user796443
user796443

Reputation:

getting data from multiple tables in single query -mysql

I use these query to select user_ids of users who have similar selection of books read.

SELECT r2.user_id
FROM `read` r1
JOIN `read` r2
ON r1.user_id <> r2.user_id AND r1.book_id = r2.book_id
WHERE r1.user_id = 1
GROUP BY r2.user_id
HAVING count(*) >= 5

But I don't want to simply display user_id. But also data about this user_id from other tables!

Query above uses only this table:

CREATE TABLE `read` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) unsigned NOT NULL,
  `book_id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `No duplicates` (`user_id`,`book_id`),
  KEY `book_id` (`book_id`),
  CONSTRAINT `connections_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `connections_ibfk_2` FOREIGN KEY (`book_id`) REFERENCES `books` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

But I also have:

CREATE TABLE `users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `email` char(255) NOT NULL DEFAULT '',
  `password` char(12) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

and

CREATE TABLE `books` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `book` char(55) NOT NULL DEFAULT '',
  `user_id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `book` (`book`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `books_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

So for every user_id in the list generated by my query, I want also to have email from users. And all book_ids from read for that user, but these book_ids are ids and I want that to show book from books based on book_ids.

Wow, will anyone understand what I wrote?

:L)

Upvotes: 0

Views: 1295

Answers (2)

AgRizzo
AgRizzo

Reputation: 5271

Try this. Hopefully no syntax errors. If it doesn't work, please go to SQLFiddle.com and load up some test data

SELECT users.id, users.email, book.books
FROM (
  SELECT r2.user_id
  FROM `read` r1
  JOIN `read` r2
    ON r1.user_id <> r2.user_id 
      AND r1.book_id = r2.book_id
  WHERE r1.user_id = 1
  GROUP BY r2.user_id
  HAVING count(*) >= 5) as t1
 JOIN users
   ON t1.user_id = users.id
 JOIN read
   ON read.user_id = t1.user_id
 JOIN books
   ON books.id = read.book_id
 WHERE EXISTS(SELECT * 
              FROM read 
              WHERE read.user_id = 1 
                AND read.book_id = book.id)

To summarize with a list - sorted by books in common descending:

SELECT users.id, users.email, t1.qty, GROUP_CONCAT(book.books)
FROM (
  SELECT r2.user_id, COUNT(*) AS qty
  FROM `read` r1
  JOIN `read` r2
    ON r1.user_id <> r2.user_id 
      AND r1.book_id = r2.book_id
  WHERE r1.user_id = 1
  GROUP BY r2.user_id
  HAVING count(*) >= 5) as t1
 JOIN users
   ON t1.user_id = users.id
 JOIN read
   ON read.user_id = t1.user_id
 JOIN books
   ON books.id = read.book_id
 WHERE EXISTS(SELECT * 
              FROM read 
              WHERE read.user_id = 1 
                AND read.book_id = book.id)
 GROUP BY users.id, users.email, t1.qty
 ORDER BY t1.qty DESC, users.email ASC

Upvotes: 0

Sid M
Sid M

Reputation: 4354

Try this

    SELECT r2.user_id,u1.email,b1.books
    FROM `read` r1
    JOIN `read` r2
    ON r1.user_id <> r2.user_id AND r1.book_id = r2.book_id
    JOIN `users` u1
    ON r2.user_id =u1.id
    JOIN `books` b1
    ON r1.book_id=b1.id
    WHERE r1.user_id = 1
    GROUP BY r2.user_id
    HAVING count(*) >= 5

Upvotes: 0

Related Questions