CODEWITHSUNDEEP
mysql
Dexy_Wolf
Dexy_Wolf

Reputation: 999

MySQL query for searching subset of two tables

I have two tables:

CREATE TABLE IF NOT EXISTS `comments` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `photograph_id` int(11) NOT NULL,
    `created` datetime NOT NULL,
    `author` varchar(255) NOT NULL,
    `body` text NOT NULL,
    `email` varchar(255) NOT NULL,
    `liked` int(11) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `photograph_id` (`photograph_id`)
)

And this:

CREATE TABLE IF NOT EXISTS `photographs` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `user_id` int(11) NOT NULL,
    `filename` varchar(255) NOT NULL,
    `type` varchar(100) NOT NULL,
    `size` int(11) NOT NULL,
    `caption` varchar(255) NOT NULL,
    `liked` int(11) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `user_id` (`user_id`)
)

I am having trouble merging these two into one query. In this query I would like to have sorting call of number of comments that every photo have. In Comments table, there is column photograph_id, that links to the photo id in Photographs table. Thanks for the help.

Upvotes: 1

Views: 360

Answers (2)

Johan
Johan

Reputation: 76567

For photo's with 1 or more comments do:

SELECT p.id, COUNT(*) as commentcount FROM photographs p
INNER JOIN comments c ON (p.id = c.photograph_id)
GROUP BY p.id
ORDER BY commentcount DESC

If you also want photo's with zero comments do:

SELECT p.id, COUNT(c.id) as commentcount FROM photographs p
LEFT JOIN comments c ON (p.id = c.photograph_id)
GROUP BY p.id
ORDER BY commentcount DESC

Upvotes: 2

Ariel
Ariel

Reputation: 26753

SELECT *,
       (SELECT COUNT(*) FROM comments
        WHERE photographs.id = ccomments.photograph_id)) commentcount
FROM photographs

This will probably be faster than the join method. Maybe. You will need to experiment.

Upvotes: 0

Related Questions