How do i write a SQL statement with mutliple test and tables?

Question

-edit- Thanks everyone i figured it out with your suggestions and syntax you wrote.

This is what i am attempting to do but for the life of me i cant figure out how to write the SQL statement (sqlite until prototype is done, will switch to another SQL which i havent decided upon yet).

        Solution:
        command.CommandText =
            "SELECT mc.date " +
            "FROM media_comment AS mc "+
            "LEFT JOIN media_comment AS mc2 ON mc.parentId = mc2.id AND mc2.author=@userId " +
            "LEFT JOIN media AS m ON m.mediaId = mc.mediaId " +
            "WHERE (mc.author=@userId AND mc.date>@date) AND(" +
                "(mc.parentId == 0 AND mc.mediaId == m.mediaId AND m.userId!=@userId) OR " +
                "(mc.parentId == mc2.id AND mc2.author=@userId)" +
            ");";

original SQL with errors and wrong syntax:
//m = media
//mc2 = media_comment but a different row
SELECT date FROM media_comment AS mc
WHERE (mc.author=@userId AND mc.date>@date)
  WHERE 
    (mc.parentId == 0 AND mc.mediaId == m.mediaId AND m.userId!=@userId) OR
    (mc.parentId == mc2.id AND mc2.author=@userId)

Answer 1

If I understand what you want, you must put two times the table in the from with different alias:

SELECT date FROM media_comment AS mc, media_comment AS m 
WHERE (mc.author=@userId AND mc.date>@date)
  AND
    ((mc.parentId == 0 AND mc.mediaId == m.mediaId AND m.userId!=@userId) OR
    (mc.parentId == mc2.id AND mc2.author=@userId))

Answer 2

SELECT 
    date 
FROM 
    media_comment AS mc, 
    media_comment AS m 
WHERE (mc.author=@userId AND mc.date>@date)
  AND 
    (
        (mc.parentId == 0 AND mc.mediaId == m.mediaId AND m.userId!=@userId) 
    OR
        (mc.parentId == mc2.id AND mc2.author=@userId)
    )

Answer 3

I'm not sure if I fully understand the business rules behind the query but I see a couple of problems:

The tables associated with the aliases m and mc2 are not declared There are two where clauses

SELECT date
FROM media_comment mc
JOIN media m
ON m.mediaID = mc.mediaId
WHERE mc.author = @userID and mc.date > @date
AND ((mc.parentID = 0 AND m.userId != @userId) 
OR (exists select 1 from media_comment mc2 where mc.parentID = mc2.id AND mc2.author = mc.author))

This query gets the date of a comment that meets the following criteria

Is written by the author specified in @author and was posted after the date @date and meets either of the following two criteria:

A) Comment was root comment (no parent) was introduced by someone other than the person that created the media item

B) Comment is a child commemt of a comment written by @userId

Answer 4

First off, your question isn't very clear and that seems to be causing a lot of confusion.

Secondly, we need more context; where are the other variables coming from? (m and mc2) Can you show us the queries or defines that create them?

If you can address those issues I think you'll get much better results from your question.