CODEWITHSUNDEEP
sqlpostgresql
abdelrahman khalil
abdelrahman khalil

Reputation: 123

Need help in connecting two tables without exactly the same info in the joined tables

I have two tables: articles and log

the articles table has these columns

example

title                                    | slug
-----------------------------------------|----------------------------
cats are cute and wonderfully adorable   | cats-are-cute

log table has columns:

path                          |  id 
------------------------------|--------
/article/cats-are-cute        |  123

I need a query to find What are the most popular three articles of all time

I tried this

select articles.title, count(log.id) as views
from articles
    right join log on log.path like concat('/article/',articles.slug)
group by articles.title
order by views desc limit 3;

it doesn't show correct values.

both answers below are correct, but the one i chose takes less processing time

Upvotes: 1

Views: 62

Answers (2)

DxTx
DxTx

Reputation: 3357

Try this one

SELECT articles.title, tmp.views
FROM   (SELECT path, Count(path) AS views
        FROM   log
        GROUP  BY path) tmp
       INNER JOIN articles ON tmp.path = Concat('/article/', articles.slug)
ORDER  BY tmp.views DESC
LIMIT  3;

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521914

It sounds like you want to check if the slug column of the articles table is contained within the path of the log table. We can try using a POSIX regex in Postgres to handle that:

SELECT
    a.title,
    COUNT(l.id) AS views
FROM articles a
LEFT JOIN log l
    ON l.path ~ CONCAT('.*\y', a.slug, '\y.*')
GROUP BY
    a.title
ORDER BY
    views DESC
LIMIT 3;

This would match any path which contains, as an exact path component, the slug from the corresponding article.

Upvotes: 1

Related Questions