Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

issue returning all information from chat sytem that has no replies, just its some sort of JOIN problem?

Posted on 2014-11-06
25
Medium Priority
?
111 Views
Last Modified: 2014-11-10
Hi

As part of a system I'm creating I have a messaging feature almost identical to FaceBook (as lots of people know the look and feel of that)

Currently displaying latest message where conversation has 1 or more replies
All works fine if a user has had a reply to their message but I'm unable to pull back the latest message in the conversations where a conversation has been started.

The 3rd line "Leigh" has no replies as yet and doesnt show which I need it to?
I'm guessing it’s something simple for a guru but it’s got me well and truly stuck.

There's a live example on SQL Fiddle at http://sqlfiddle.com/#!2/854e3/1/0 which shows both Jayne and Clare and the last message they sent or received, there should also be on for user 3 - Leigh "Hi Leigh, hows it looking on Android?" but that's not showing

I'm after these results, as well as seeing the single message to Leigh please



CREATE TABLE conversation (
  coID int auto_increment primary key,
  coUserOne int(11),
  coUserTwo int(11),
  coIP varchar(30),
  coTime int(11)
); 


CREATE TABLE users (
  uID int auto_increment primary key,
  uScreenName varchar(100),
  uProfileImage varchar(100),
  uSex varchar(10)
); 


CREATE TABLE conversation_reply (
  crID int auto_increment primary key,
  crReply text,
  crUserFK int(11),
  crIP varchar(30),
  crTime int(11),
  crConFK int(11)
); 


INSERT INTO users VALUES (1,'Neil','20140302__1__b00c1338a7feeae110af30868d871549.png','Male');
INSERT INTO users VALUES (2,'Clare','20140303__2__150fb4811b2794f6b8a39d49ead2edc5.jpg','Female');
INSERT INTO users VALUES (3,'Leigh','20140403__3__fda33411ff84b3f69484d862838dd417.jpg','Male');
INSERT INTO users VALUES (7,'Jayne','20140403__7__87bbf84f78bd08b71a55a2002e35230a.jpg','Female');


INSERT INTO conversation VALUES (1,1,2,'127.0.0.1',1400945511);
INSERT INTO conversation VALUES (2,1,3,'127.0.0.1',1400945613);
INSERT INTO conversation VALUES (3,1,7,'127.0.0.1',1400946146);


INSERT INTO conversation_reply VALUES (1,'This is my first conversation to Clare',1,'127.0.0.1',1400945511,1);
INSERT INTO conversation_reply VALUES (2,'this is a reply from Clare',2,'127.0.0.1',1400945514,1);
INSERT INTO conversation_reply VALUES (3,'Hi Leigh, hows it looking on Android?',1,'127.0.0.1',1400945613,2);
INSERT INTO conversation_reply VALUES (5,'Have you seen the amount of people who have registered on the site in the last day, it\'s gone mad.',1,'127.0.0.1',1400946146,3);
INSERT INTO conversation_reply VALUES (6,'test',7,'127.0.0.1',1400946149,3);
INSERT INTO conversation_reply VALUES (7,'good',1,'92.40.104.96',1401642550,3);
INSERT INTO conversation_reply VALUES (9,'Very good',1,'188.31.214.125',1401703530,3);
INSERT INTO conversation_reply VALUES (10,'latest test from upload to live server :)',1,'82.47.69.204',1401991923,1);
INSERT INTO conversation_reply VALUES (11,'Cooooooooooooooo eeeeeeeeee',2,'77.96.207.54',1401993721,1);
INSERT INTO conversation_reply VALUES (12,'Ipad test',1,'188.29.102.172',1402780028,1);

Open in new window


SELECT a.crConFK, (
	SELECT crTime 
	FROM conversation_reply 
	WHERE crID = (
		SELECT max(crID) 
			FROM conversation_reply 
			WHERE crConFK = a.crConFK)) 
			AS crTime, (
				SELECT crReply 
				FROM conversation_reply 
				WHERE crID = (
					SELECT max(crID)
					FROM conversation_reply 
					WHERE crConFK = a.crConFK)) 
					AS crReply, uScreenName, uProfileImage, uID, uSex
FROM users,conversation_reply a
WHERE a.crUserFK = uID
AND uID != 1
AND a.crConFK IN (SELECT coID FROM conversation WHERE (coUserOne = 1 OR coUserTwo = 1) )
GROUP BY uID 
ORDER BY a.crTime DESC

Open in new window

0
Comment
Question by:Neil Thompson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 14
  • 10
25 Comments
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40428161
Which RDBMS are you working with?
0
 
LVL 3

Author Comment

by:Neil Thompson
ID: 40428174
This is on MySQL 5.6
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40428177
Why are you using a GROUP BY clause?
Did you try without it?
SELECT a.crConFK, (
	SELECT crTime 
	FROM conversation_reply 
	WHERE crID = (
		SELECT max(crID) 
			FROM conversation_reply 
			WHERE crConFK = a.crConFK)) 
			AS crTime, (
				SELECT crReply 
				FROM conversation_reply 
				WHERE crID = (
					SELECT max(crID)
					FROM conversation_reply 
					WHERE crConFK = a.crConFK)) 
					AS crReply, uScreenName, uProfileImage, uID, uSex
FROM users,conversation_reply a
WHERE a.crUserFK = uID
AND uID != 1
AND a.crConFK IN (SELECT coID FROM conversation WHERE (coUserOne = 1 OR coUserTwo = 1) )
ORDER BY a.crTime DESC

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 3

Author Comment

by:Neil Thompson
ID: 40428187
I've just removed it and it shows message 1 twice if I copy your code above into the sqlfiddle page http://sqlfiddle.com/#!2/854e3/2/0
0
 
LVL 3

Author Comment

by:Neil Thompson
ID: 40428188
To be honest I've fudged my way to get it this far :)
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40428189
Even I didn't get your data model.
By your example there are 3 conversations and the one you posted here doesn't looks to me part of the same conversation.
--CONVERSATION 1
crID	crReply	crUserFK	crIP	crTime	crConFK
1	This is my first conversation to Clare	1	127.0.0.1	1400945511	1
2	this is a reply from Clare	2	127.0.0.1	1400945514	1
10	latest test from upload to live server :)	1	82.47.69.204	1401991923	1
11	Cooooooooooooooo eeeeeeeeee	2	77.96.207.54	1401993721	1
12	Ipad test	1	188.29.102.172	1402780028	1

--CONVERSATION 2
crID	crReply	crUserFK	crIP	crTime	crConFK
3	Hi Leigh, hows it looking on Android?	1	127.0.0.1	1400945613	2

--CONVERSATION 3
crID	crReply	crUserFK	crIP	crTime	crConFK
5	Have you seen the amount of people who have registered on the site in the last day, it's gone mad.	1	127.0.0.1	1400946146	3
6	test	7	127.0.0.1	1400946149	3
7	good	1	92.40.104.96	1401642550	3
9	Very good	1	188.31.214.125	1401703530	3

Open in new window

0
 
LVL 3

Author Comment

by:Neil Thompson
ID: 40428198
ITs conversation 2 line 11 above that isn't showing for me?

The last message for conversation 1 and 3 are fine (line 7 Ipad test) and (line 18 (Very good)

Yes, there are 3 conversations
0
 
LVL 3

Author Comment

by:Neil Thompson
ID: 40428199
It should only be getting the last message on each one (whether its a reply, or the original message)
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40428203
Ok. If you want the last message for each conversation then try this code:
SELECT c1.* 
FROM conversation_reply AS c1
	INNER JOIN (SELECT crConFK, MAX(crID) MAXcrID
			FROM conversation_reply
			GROUP BY crConFK) AS c2
		ON c1.crID=c2.MAXcrID
ORDER BY c1.crID, c1.crconfk

Open in new window

0
 
LVL 3

Author Comment

by:Neil Thompson
ID: 40428207
That's getting there but its not returning the users profile picture of screen name now?
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40428217
I didn't join the users table. Here it's the complete solution:
SELECT c1.crConFK, c1.crTime, c1.crReply, u.uScreenName, u.uProfileImage, u.uID, u.uSex
FROM conversation_reply AS c1
	INNER JOIN (SELECT crConFK, MAX(crID) MAXcrID
			FROM conversation_reply
			GROUP BY crConFK) AS c2
		ON c1.crID=c2.MAXcrID
	INNER JOIN users u
		ON c1.crUserFK=u.uID
ORDER BY c1.crID, c1.crconfk

Open in new window

0
 
LVL 3

Author Comment

by:Neil Thompson
ID: 40428222
nearly :) but its only returning my name and profile image, not the name / profile image associated with the conversations

Getting this:
getting this
But the names associated with each message should be as the 2nd image on this page
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40428225
Just realized that also. So need joining with Conversation table and then with Users table.
Check now:
SELECT c1.crConFK, c1.crTime, c1.crReply, u.uScreenName, u.uProfileImage, u.uID, u.uSex
FROM conversation_reply AS c1
	INNER JOIN (SELECT crConFK, MAX(crID) MAXcrID
			FROM conversation_reply
			GROUP BY crConFK) AS c2
		INNER JOIN conversation c
				INNER JOIN users u ON c.coUserTwo=u.uID
		ON c.coID=c2.crConFK
	ON c1.crID=c2.MAXcrID
ORDER BY c1.crID, c1.crconfk

Open in new window

0
 
LVL 52

Accepted Solution

by:
Vitor Montalvão earned 2000 total points
ID: 40428231
Forgot that's MySQL. Here's the correct version:
SELECT c1.crConFK, c1.crTime, c1.crReply, u.uScreenName, u.uProfileImage, u.uID, u.uSex
FROM conversation_reply AS c1
	INNER JOIN (SELECT crConFK, MAX(crID) MAXcrID
			FROM conversation_reply
			GROUP BY crConFK) AS c2
    ON c1.crID=c2.MAXcrID
	INNER JOIN conversation c ON c.coID=c2.crConFK
    INNER JOIN users u ON c.coUserTwo=u.uID
ORDER BY c1.crID, c1.crconfk

Open in new window

0
 
LVL 3

Author Closing Comment

by:Neil Thompson
ID: 40428240
Vitor you are a true star. Thank you so much for this, its given me headaches for weeks :)
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40428250
Thanks for the challenge ;)
Cheers.
0
 
LVL 3

Author Comment

by:Neil Thompson
ID: 40428255
Thanks sdstuber ill remember that in future
0
 
LVL 3

Author Comment

by:Neil Thompson
ID: 40428288
I've just realised there is a 2nd part to this question which I forgot to add in the original request and that is the conversations returned should only relate to the currently logged in user ID

e.g. if user 3 is logged in, only messages from him to others, or from others to him should show (not all of them)

I'll open another question now.
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40428296
Just add a WHERE clause with the corresponding user ID.
0
 
LVL 3

Author Comment

by:Neil Thompson
ID: 40428300
I've tried adding it in a few places but it errors in fiddle? guess im just misplacing it. (LINE 6 is the code I had)

Where for example would I add this (this is what I used before)

[/SELECT a.crConFK, (SELECT crTime FROM conversation_reply WHERE crID = (SELECT max(crID) FROM conversation_reply WHERE crConFK = a.crConFK)) AS crTime, (SELECT crReply FROM conversation_reply WHERE crID = (SELECT max(crID) FROM conversation_reply WHERE crConFK = a.crConFK)) AS crReply, uScreenName, uProfileImage, uID, uSex
		FROM users,conversation_reply a
		LEFT JOIN conversation_reply AS b on b.crID = a.crID 
		WHERE a.crUserFK = uID
		AND uID != ?
		[b]AND a.crConFK IN (SELECT coID FROM conversation WHERE (coUserOne = ? OR coUserTwo = ?) )[/b]
		GROUP BY uID 
		ORDER BY a.crTime DESC

Open in new window


It has to narrow down the results to just the conversations user x (1 for example) is involved in
0
 
LVL 3

Author Comment

by:Neil Thompson
ID: 40428305
I as user 1 would have 3 conversations on the go, whereas users 2,3 and 7 would only have 1 each
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40428316
Based on your original post here's the solution with the WHERE clause (just replace the 1 for the userID that you want):
SELECT c1.crConFK, c1.crTime, c1.crReply, u.uScreenName, u.uProfileImage, u.uID, u.uSex
FROM conversation_reply AS c1
	INNER JOIN (SELECT crConFK, MAX(crID) MAXcrID
			FROM conversation_reply
			GROUP BY crConFK) AS c2
    ON c1.crID=c2.MAXcrID
	INNER JOIN conversation c ON c.coID=c2.crConFK
    INNER JOIN users u ON c.coUserTwo=u.uID
WHERE (c.coUserOne = 1 OR c.coUserTwo = 1)
ORDER BY c1.crID, c1.crconfk

Open in new window

0
 
LVL 3

Author Comment

by:Neil Thompson
ID: 40428318
Many thanks again Vitor, I was adding it into the INNER JOINS (scary when you don't know what you're doing ) :)
0
 
LVL 3

Author Comment

by:Neil Thompson
ID: 40432564
For info, there is a continuation to this question as I've noticed something else needed which is the user displaying should always be the other person in the chat, not the logged in user:

http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_28554211.html
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question