CODEWITHSUNDEEP
mysqljoin
Zerus
Zerus

Reputation: 158

SQL referring on one table after join with another table

I got this MySQL query to output latest chat message a user with ID of 18 has had with other users. The query works until I add-in last JOIN, intended to calculate amount of unviewed msgs (unviewed_total). The error I get is 'column not found' for T2.rid1 and T2.rid2. Can't figure out how to refer these columns in a proper way.

SELECT T2.maxDate, T2.ava, T2.uname,chat.user_to,chat.user_from,chat.body,chat.request_id,chat.secondary_rid, T3.unviewed_total FROM 
                (SELECT T1.user2_id, users.uname, users.ava, max(cdate) maxDate, T1.rid rid1, T1.sec_rid rid2 FROM
                (SELECT chat.user_to user2_id, max(msg_time) cdate,request_id rid,secondary_rid sec_rid
                    FROM chat WHERE chat.user_from=18
                    GROUP BY chat.user_to
                union distinct
                (SELECT chat.user_from user2_id, max(msg_time) cdate,request_id rid,secondary_rid sec_rid
                    FROM chat WHERE chat.user_to=18
                    GROUP BY chat.user_from)) T1
                inner join users on (users.uid = T1.user2_id)
                group by T1.user2_id
                order by maxDate desc) T2
            join chat on (T2.maxDate = chat.msg_time)  
            join (SELECT COUNT(viewed) unviewed_total FROM chat WHERE viewed='0' AND user_to=18 AND request_id IN (T2.rid1,T2.rid2)) T3
            ORDER BY T2.maxDate DESC

Upvotes: 1

Views: 47

Answers (1)

Vincent Beltman
Vincent Beltman

Reputation: 2104

The inner query of that join doesn't know about the table T2. I suggest moving the join to the select statement.

Change

SELECT T2.maxDate, T2.ava, T2.uname,chat.user_to,chat.user_from,chat.body,chat.request_id,chat.secondary_rid, T3.unviewed_total FROM

to

SELECT T2.maxDate, T2.ava, T2.uname,chat.user_to,chat.user_from,chat.body,chat.request_id,chat.secondary_rid, (SELECT COUNT(viewed) unviewed_total FROM chat WHERE viewed='0' AND user_to=18 AND request_id IN (T2.rid1,T2.rid2)) unviewed_total

More about this technique here: https://www.essentialsql.com/get-ready-to-learn-sql-server-20-using-subqueries-in-the-select-statement/

Upvotes: 1

Related Questions