CODEWITHSUNDEEP
mysqlsql
Shalva Kakauridze
Shalva Kakauridze

Reputation: 1235

Mysql select alias in where clause

I have problem with using alias in where clause.

I have tables like this: enter image description here

I store users and they can send messages to each other. Messages data is stored in social_messages table, with core_users_sender and core_users_receiver ids.

Now when user logs in system I want to show the list of only those users with which he/she had conversation.

(logged in core_users.id is 6) I use this query and get ids of friends with which user had conversations without problem:

SELECT
    messages.id,
    messages.status,
    messages.send_date,
    IF(
        core_users_sender = 6,
            core_users_receiver,
            core_users_sender
    ) as friend_id
FROM
    social_messages messages
WHERE
      messages.core_users_sender = 6
        OR
      messages.core_users_receiver = 6
GROUP BY
  friend_id

But problem is that when I try to get data from core_users table with friend_id and and use query:

SELECT
    messages.id,
    messages.status,
    messages.send_date,
    IF(
        core_users_sender = 6,
            core_users_receiver,
            core_users_sender
    ) as friend_id,
    users.fullname
FROM
    social_messages messages,
    core_users users
WHERE
      users.id = friend_id
      AND
      (
          messages.core_users_sender = 6
            OR
          messages.core_users_receiver = 6
      )
GROUP BY
  friend_id

I get error because friend_id cant be used in where clause because its calculated in select

Upvotes: 0

Views: 176

Answers (3)

Shiplu Mokaddim
Shiplu Mokaddim

Reputation: 57690

You can alias your table and use it

        SELECT messages.*, users.fullname
        FROM
        (SELECT
            messages.id,
            messages.status,
            messages.send_date,
            IF(
                core_users_sender = 6,
                    core_users_receiver,
                    core_users_sender
            ) as friend_id
        FROM
            social_messages messages
        WHERE
              messages.core_users_sender = 6
                OR
              messages.core_users_receiver = 6
        GROUP BY
          friend_id) as `messages`
        JOIN
              core_users users
        ON
              users.id = messages.friend_id

Upvotes: 0

Ryx5
Ryx5

Reputation: 1366

I suggest you to reorder your query in this way :

SELECT
    messages.id,
    messages.status,
    messages.send_date,
    users.fullname,
    IF(
        messages.core_users_sender IS NOT NULL,
        messages.core_users_receiver,
        messages.core_users_sender
    ) as friend_id
FROM
    social_messages messages
    left join core_users users_sender
        on users_sender.id = messages.core_users_sender
        and messages.core_users_sender = 6
    left join core_users users_receiver
        on users_receiver.id = messages.core_users_receiver 
        and messages.core_users_receiver = 6

WHERE
    users_sender.id IS NOT NULL OR users_receiver.id IS NOT NULL

Upvotes: 0

juergen d
juergen d

Reputation: 204904

You can't uses aliases in the where clause. Use the original subquery

where users.id = IF(
                core_users_sender = 6,
                core_users_receiver,
                core_users_sender
        )

It is not allowable to refer to a column alias in a WHERE clause, because the column value might not yet be determined when the WHERE clause is executed.

Upvotes: 5

Related Questions