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

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

LVL 3
Neil ThompsonSenior Systems DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Which RDBMS are you working with?
0
Neil ThompsonSenior Systems DeveloperAuthor Commented:
This is on MySQL 5.6
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Neil ThompsonSenior Systems DeveloperAuthor Commented:
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
Neil ThompsonSenior Systems DeveloperAuthor Commented:
To be honest I've fudged my way to get it this far :)
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
Neil ThompsonSenior Systems DeveloperAuthor Commented:
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
Neil ThompsonSenior Systems DeveloperAuthor Commented:
It should only be getting the last message on each one (whether its a reply, or the original message)
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
Neil ThompsonSenior Systems DeveloperAuthor Commented:
That's getting there but its not returning the users profile picture of screen name now?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
Neil ThompsonSenior Systems DeveloperAuthor Commented:
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
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Neil ThompsonSenior Systems DeveloperAuthor Commented:
Vitor you are a true star. Thank you so much for this, its given me headaches for weeks :)
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Thanks for the challenge ;)
Cheers.
0
Neil ThompsonSenior Systems DeveloperAuthor Commented:
Thanks sdstuber ill remember that in future
0
Neil ThompsonSenior Systems DeveloperAuthor Commented:
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
Vitor MontalvãoMSSQL Senior EngineerCommented:
Just add a WHERE clause with the corresponding user ID.
0
Neil ThompsonSenior Systems DeveloperAuthor Commented:
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
Neil ThompsonSenior Systems DeveloperAuthor Commented:
I as user 1 would have 3 conversations on the go, whereas users 2,3 and 7 would only have 1 each
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
Neil ThompsonSenior Systems DeveloperAuthor Commented:
Many thanks again Vitor, I was adding it into the INNER JOINS (scary when you don't know what you're doing ) :)
0
Neil ThompsonSenior Systems DeveloperAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.