10 Very Useful SQL Queries with JOINS

The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables. [@via w3schools]

A SQL JOIN clause combines records from two or more tables in a database. It creates a set that can be saved as a table or used as is. A JOIN is a means for combining fields from two tables by using values common to each. ANSI standard SQL specifies four types of JOINs: INNER, OUTER, LEFT, and RIGHT. In special cases, a table (base table, view, or joined table) can JOIN to itself in a self-join. [@via wikipedia]

SQL Joins are helpful when we have to fetch data with a single query from two or more database tables.

Case:

I need to store news/article information. The news can also have comments and tags. News can be submitted/posted by multiple users.

According to the above scenario, I have created 5 tables and inserted some data into them :-

news – to store news
comments – to store comments for any particular news
tags – to store tags associated with any particular news
users – to store user information
tags_news – to store relationship between news and tags

—
— Table structure for table comments
—

CREATE TABLE comments (
id bigint(20) NOT NULL auto_increment,
news_id bigint(20) NOT NULL,
user_id bigint(20) NOT NULL,
detail text NOT NULL,
status tinyint(1) NOT NULL default '1',
comment_date int(11) NOT NULL,
PRIMARY KEY (id),
KEY user_id (user_id),
KEY news_id (news_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;


—
— Dumping data for table comments
—

INSERT INTO comments (id, news_id, user_id, detail, status, comment_date) VALUES
(1, 1, 2, 'nice post :)', 1, 1273431296),
(2, 2, 2, 'hahaha', 1, 1273431420),
(3, 3, 2, '3123123', 1, 1273431452),
(4, 1, 2, 'thank You', 1, 1273431475),
(5, 2, 1, 'congratulations!', 1, 1273431500);

— ——————————————————–

—
— Table structure for table news
—

CREATE TABLE news (
id bigint(20) NOT NULL auto_increment,
user_id bigint(20) NOT NULL,
title text NOT NULL,
detail text NOT NULL,
visit int(11) NOT NULL default '0',
status tinyint(1) NOT NULL default '1',
created_date int(11) NOT NULL,
PRIMARY KEY (id),
KEY user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

—
— Dumping data for table news
—

INSERT INTO news (id, user_id, title, detail, visit, status, created_date) VALUES
(1, 1, 'Extra Content', 'fasdfasdf', 2, 1, 1273431296),
(2, 1, 'My My Question', 'this is jpt question.. :D', 16, 1, 1273431389),
(3, 2, 'Am I ram?', 'I am ram..yahoo !!', 3, 1, 1273431420);

— ——————————————————–

—
— Table structure for table tags
—

CREATE TABLE tags (
id bigint(20) NOT NULL auto_increment,
name varchar(255) NOT NULL,
slug varchar(255) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

—
— Dumping data for table tags
—

INSERT INTO tags (id, name, slug) VALUES
(1, 'sagarmatha', 'sagarmatha'),
(2, 'nepal', 'nepal'),
(3, 'gautam buddha', 'gautam-buddha'),
(4, 'testing', 'testing'),
(5, 'tags', 'tags'),
(6, 'tasty apple', 'tasty-apple'),
(7, 'banana', 'banana');

— ——————————————————–

—
— Table structure for table tags_news
—

CREATE TABLE tags_news (
id bigint(20) NOT NULL auto_increment,
news_id bigint(20) NOT NULL,
tags_id bigint(20) NOT NULL,
PRIMARY KEY (id),
KEY news_id (news_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

—
— Dumping data for table tags_news
—

INSERT INTO tags_news (id, news_id, tags_id) VALUES
(1, 1, 1),
(2, 1, 2),
(3, 1, 3),
(4, 2, 4),
(5, 2, 5),
(6, 2, 6),
(7, 3, 6),
(8, 3, 7);

— ——————————————————–

—
— Table structure for table users
—

CREATE TABLE users (
id bigint(20) NOT NULL auto_increment,
user_id bigint(20) NOT NULL,
firstname varchar(100) NOT NULL,
lastname varchar(100) NOT NULL,
PRIMARY KEY (id),
KEY user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

—
— Dumping data for table users
—

INSERT INTO users (id, user_id, firstname, lastname) VALUES
(1, 1, 'Mukesh', 'Chapagain'),
(2, 2, 'Christopher', 'Gayle'),
(3, 3, 'Brian', 'Lara');

— ——————————————————–

Now, by using SQL JOINS, I can fetch data from these tables for different conditions and in different ways. Here follows the different SQL Queries with the magical JOIN statements:-

1) SELECT NEWS WITH COMMENTS COUNT FOR EACH NEWS


SELECT n.*, ifnull(c.count,0) AS comment FROM news AS n
LEFT JOIN
(SELECT COUNT(comments.id) AS count, comments.news_id FROM comments GROUP BY comments.news_id) AS c
ON n.id = c.news_id
ORDER BY n.created_date DESC;


2) SELECT NEWS POSTED BY ANY PARTICULAR USER (HERE, user_id = 1)


SELECT n.* FROM news AS n
INNER JOIN
users AS u
ON n.user_id = u.id
WHERE u.id = 1;


3) SELECT NEWS POSTED BY ANY PARTICULAR USER (WITH USER’S FIRSTNAME AND LASTNAME) (HERE, user_id = 1)


SELECT n.*, u.firstname AS firstname, u.lastname AS lastname FROM news AS n
INNER JOIN
users AS u
ON n.user_id = u.id
WHERE u.id = 1;


4) SELECT NEWS POSTED BY ANY PARTICULAR USER (WITH COMMENTS COUNT FOR EACH NEWS) (HERE, user_id = 1)


SELECT n.*, ifnull(c.count,0) AS comment, u.firstname AS firstname, u.lastname AS lastname FROM news AS n
LEFT JOIN
(SELECT COUNT(comments.id) AS count, comments.news_id FROM comments GROUP BY comments.news_id) AS c
ON n.id = c.news_id
INNER JOIN
users AS u
ON n.user_id = u.id
WHERE u.id = 1;


5) SELECT COMMENTS FOR ANY PARTICULAR NEWS (HERE, news_id =1)


SELECT c.* FROM comments AS c
INNER JOIN
news AS n
ON c.news_id = n.id
WHERE c.news_id = 1;


6) SELECT COMMENTS FOR ANY PARTICULAR NEWS (ALONG WITH USER INFORMATION) (HERE, news_id = 1)


SELECT c.*, u.firstname AS firstname, u.lastname AS lastname FROM comments AS c
INNER JOIN
news AS n
ON c.news_id = n.id
INNER JOIN
users AS u
ON c.user_id = u.id
WHERE c.news_id = 1;


7) SELECT TAGS FOR ANY PARTICULAR NEWS (HERE, news_id = 1)


SELECT t.* FROM tags AS t
INNER JOIN
(SELECT tags_news.news_id AS news_id, tags_news.tags_id FROM tags_news WHERE tags_news.news_id = 1) AS tn
ON t.id = tn.tags_id;


8) SELECT NEWS BY TAG NAME (HERE, tag = ‘nepal’)


SELECT n.*, t.name AS tag_name, t.slug AS tag_slug FROM news AS n
INNER JOIN
tags_news as tn
ON n.id = tn.news_id
INNER JOIN tags AS t
ON tn.tags_id = t.id
WHERE t.slug = 'nepal';


9) SELECT NEWS BY TAG NAME (ALONG WITH COMMENTS COUNT) (HERE, tag = ‘nepal’)


SELECT n.*, ifnull(c.count,0) AS comment, t.name AS tag_name, t.slug AS tag_slug, t.id AS tag_id FROM news AS n
INNER JOIN
tags_news AS tn
ON (n.id = tn.news_id)
INNER JOIN tags AS t
ON (tn.tags_id = t.id)
LEFT JOIN
(SELECT COUNT(comments.id) AS count, comments.news_id FROM comments
GROUP BY comments.news_id) AS c
ON n.id = c.news_id
WHERE t.slug = 'nepal'
ORDER BY n.created_date DESC;


10) SELECT TAG CLOUD


SELECT t . * , IFNULL( tq.count, 0 ) AS count
FROM tags AS t
LEFT JOIN (
SELECT COUNT( tags_news.id ) AS count, tags_news.tags_id
FROM tags_news
GROUP BY tags_news.tags_id
) AS tq ON t.id = tq.tags_id
ORDER BY t.name ASC
LIMIT 15;

Hope this helps. And thanks for reading.