MYSQL select from additional tables in database, match data etc

I have this query that works just fine, however I want to change my database table a little bit, so I need to change my query a little bit. My working query is this:

$notificationsq = mysql_query("
SELECT
    N.*,
    P.*,
MAX(N.date) AS newDate


 FROM
    notifications N,
    posts P

WHERE
    N.userID='$session'
AND
    (
            (
                   N.action='1'
                AND
                   (N.state = 0 OR N.state=1)
             )
            OR
                N.action='2'
     )
AND P.state='0'
AND
    N.uniqueID=P.id
GROUP BY
    N.uniqueID
ORDER BY
    N.state ASC,
    newDate DESC

") or die(mysql_error());

Now, what I'm doing is changing the row "uniqueID", so what I need to do is basically:

if(action==2){unqiqueID=C.postID} else {uniqueID=N.uniqueID}

Here is my table structure: i41.tinypic.com/nyzolg.png

The information from the photo is:

Table: Notifications

id  UserID  FromID  UniqueID    Action  State   Read_Date   Date
1   1       2       1           1       0       0           1325993600
2   1       6       2           1       0       0           1325993615
3   1       2       1           2       0       0           1325993622
4   1       6       2           2       0       0           1325993661
5   2       6       2           2       0       0           1325993661

Action = 1 means UniqueID identifies a row in Posts; Action = 2 means UniqueID identifies a row in Comments.

Table: Posts

id  ToID    FromID  Post        State   Date
1   1       2       Hey         0       1325993600
2   1       6       okay yeah   0       1325993615

Table: Comments

ID  PostID  FromID  Comment     State   Date
1   1       2       lol         0       1325993622
2   1       6       ohh         0       1325993661

So, in the Notifications table where action is 2, the UniqueID's are for the 'id' in the Comments table. What I want to return is the PostID, so in the query it would just be as if the UniqueID was this instead:

But the UniqueID would stay the same where Action is 1.

Solution

You cannot reference a calculated column in the WHERE clause of the same SELECT statement. Usually this can be worked around by using a subselect like this:

SELECT
  * /* or list the necessary columns specifically */ 
FROM (
  SELECT
    ...
    some_expression AS columnAlias,
    ...
  FROM ...
)
WHERE ...
  AND columnAlias = ...

As you can see, the column calculated by some_expression, columnAlias, can be accessed in the outer SELECT using its alias.

But you are pulling rather many columns here, and using masks too, so trying to use a subselect might cause a name clash. Try to reduce the number of columns being pulled by specifying them explicitly.

UPDATE

Based on further explanations in comments, here's what I would probably do in your situation:

SELECT
    P.id AS uniqueID2,
    MAX(N.Date) AS newDate,
    MIN(state) AS state,           /* or maybe 'AS minState', if you like */
    MAX(N.Read_Date) AS lastRead,  /* just another example to illustrate the
                                      point that most columns in this query
                                      should be selected with aggregating */
    ...  /* other columns as necessary */
FROM notifications N
    LEFT JOIN comments C ON N.action = 2 AND N.uniqueID = C.id
    INNER JOIN posts P ON N.action = 1 AND P.id = N.uniqueID
                       OR N.action = 2 AND P.id = C.postID
WHERE N.userID = '$session'
    AND (N.action = 1 AND N.state IN (0, 1) OR N.action = 2)
    AND P.state = 0
GROUP BY P.id
ORDER BY
    state ASC,    /* No 'N.' prefix here because now it is a reference
                     to the MIN(state) column, which has the same name.
                     If it looks too confusing, use a different alias for
                     the column and change this entry accordingly. */
    newDate DESC