rgb192
asked on
return all the messages with profile returned
https://www.experts-exchange.com/questions/28283780/how-did-you-hear-about-us.html?anchorAnswerId=39619770#a39619770
Currently this query returns one row of sender, message_id
| SENDER | MESSAGE_ID |
|--------|------------|
| abl | 3609276896 |
I would like to see all the rows of messages with profile_id 'abl'
Because when I check results, I have to type select * from a_messages where profile_id='abl'
Currently this query returns one row of sender, message_id
| SENDER | MESSAGE_ID |
|--------|------------|
| abl | 3609276896 |
I would like to see all the rows of messages with profile_id 'abl'
Because when I check results, I have to type select * from a_messages where profile_id='abl'
ASKER
Error Code: 1054. Unknown column 'ck.sender' in 'on clause'
? it works for me
unchanged query with result displayed
**Query 1**:
SELECT
*
FROM (
SELECT
m.sender
, m.message_id
FROM (
SELECT
profile_id
, min(message_id) AS message_id
FROM a_messages2
GROUP BY
profile_id
) AS mn
INNER JOIN a_messages2 AS m ON mn.message_id = m.message_id
WHERE m.profile_id = m.sender
) AS CK
INNER JOIN a_messages2 AS m2 ON ck.sender = m2.sender
ORDER BY
m2.profile_id
, m2.message_id ASC
**[Results][2]**:
| SENDER | MESSAGE_ID | A_MESSAGES_ID | PROFILE_ID | THIS_USER |
|--------|------------|---------------|------------|-----------|
| abl | 3609276896 | 13 | abl | staff3 |
| abl | 3609276896 | 12 | abl | staff3 |
| abl | 3609276896 | 10 | abl | staff3 |
| abl | 3609276896 | 7 | abl | staff3 |
| abl | 3609276896 | 2 | abl | staff3 |
| abl | 3609276896 | 16 | abl | staff3 |
| abl | 3609276896 | 23 | abl | staff3 |
| abl | 3609276896 | 20 | abl | staff3 |
| abl | 3609276896 | 25 | abl | staff3 |
| abl | 3609276896 | 28 | abl | staff3 |
| abl | 3609276896 | 30 | abl | staff3 |
| abl | 3609276896 | 32 | abl | staff3 |
see: http://sqlfiddle.com/#!9/8efe9/4
the "alias" CK is defined on line 19
it is then referenced in the next line "ON ck.sender..." see the ck?
perhaps try making the "alias" in lower case, so change line 19 to this:
) AS ck
it is then referenced in the next line "ON ck.sender..." see the ck?
perhaps try making the "alias" in lower case, so change line 19 to this:
) AS ck
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
lowercase ck works great.
Thanks
Thanks
ASKER
Thanks for your query.
I have a related query where I would want to see all the messages (the messages I sent and received-- not just received)
https://www.experts-exchange.com/questions/28288994/'staff3'-can-be-the-sender-which-I-want-the-query-to-show.html
I have a related query where I would want to see all the messages (the messages I sent and received-- not just received)
https://www.experts-exchange.com/questions/28288994/'staff3'-can-be-the-sender-which-I-want-the-query-to-show.html
Open in new window