CODEWITHSUNDEEP
mysqlsql
YuppieGuppie
YuppieGuppie

Reputation: 3

Passing data from parent to sub-query

Im am trying to create a query that joins on some data for each row of the main query, but I do not know how to get around the issue "Unknown column 'cm.index' in 'where clause'". How can I pass a column value of the parent query row to the sub query?

Each row in the parent query has an index integer. I want to count the number of channel_members that have a consumption index greater than the index of each message.

Here is a sample db

SELECT read_count.*, cm.*
FROM   chat_messages as cm
JOIN (SELECT b.chat_channel_id, Count(b.chat_channel_id) AS members_read 
    FROM   channel_members b
    WHERE  b.consumption_index >= cm.index 
    GROUP  BY b.chat_channel_id) read_count 
ON cm.channel_id = read_count.chat_channel_id
WHERE cm.channel_id=5;

Upvotes: 0

Views: 1952

Answers (1)

Barmar
Barmar

Reputation: 782407

A subquery that you're joining with is not a correlated subquery, so you can't pass columns from the main query into it, they have to be related in the ON condition.

You should change the subquery to include cm.index in the grouping. Then you can join on that.

SELECT cm.*, SUM(rc.members_read) AS members_read
FROM chat_messages AS cm
JOIN (SELECT chat_channel_id, consumption_index, COUNT(*) AS members_read
      FROM channel_members
      GROUP BY chat_channel_id, consumption_index) AS rc
ON cm.channel_id = rc.chat_channel_id AND rc.consumption_index >= cm.index
WHERE cm.channel_id = 5

Or you could do it as a real correlated subquery, which goes in the SELECT list.

SELECT cm.*, 
    (SELECT COUNT(*)
    FROM channel_members AS b
    WHERE b.chat_channel_id = cm.channel_id AND b.consumption_index = cm.index) AS members_read
FROM chat_messages AS cm
WHERE cm.channel_id = 5

Upvotes: 1

Related Questions