personal sql query notes

simple note

Join 3 tables and use subquery as condition

assume RoleID=2 denotes banned user and Deleted='1' denotes deleted comment, this query will return all deleted comments that posted by banned user

SELECT c.Name, b.Name, a.DateDeleted FROM forum.comment_table AS a
    LEFT JOIN forum.user_table AS b
        ON a.DeleteUserID = b.UserID
    LEFT JOIN forum.discussion_table AS c
        ON a.DiscussionID = c.DiscussionID
    WHERE a.Deleted='1'
        AND a.DateDeleted>='2012-09-01 00:00:00'
        AND a.DateDeleted<='2012-09-30 23:59:59'
        AND a.AuthUserID in (
            SELECT d.UserID FROM forum.user_table AS d
                WHERE d.RoleID=2)
    ORDER BY a.DateDeleted DESC;

Create table if not exists

CREATE TABLE IF NOT EXISTS test_table ( id BIGINT NOT NULL IDENTITY,
    firstName VARCHAR(32),
    lastName VARCHAR(32));