MySQL complicated query = brain in knots.

[Helmet]

..at least it's complicated for me I always have trouble with complex queries, but I'm determined to learn something here and not do it in multiple queries.

 

I have a table "messages", with fields threadname, xto and xfrom along with other message details. I also have a table "accounts" with username and thumbname (user photo). I would like to select all the messages in a thread, and get the username and thumbname for the xto and xfrom user id's. How can I write such a query, or is it even possible to do this all in one?

 

This is what I've tried, but it's failing and won't come close to doing what I want anyway:

 

$query = "SELECT messages.*, accounts.username, accounts.thumbname 
FROM messages, accounts 
WHERE messages.xfrom = [current user's ID passed into function] 
AND messages.account = messages.xfrom 
AND accounts.ID = messages.xfrom 
AND messages.threadid = [threadid] 
ORDER BY sent ASC";

 

I think I have to select every message in the thread and also a username and thumbname for both the sender and retriever. Do I do this by assigning a different name to these in the query somehow? Where I would end up with a result row something like:

 

messageid, threadid, xto, xfrom, xto_username, xto_thumbname, xfrom_username, xfrom_thumbname etc... ?

[able]

You need 2 instances of the accounts table, as you have 2 accounts

 

query = "SELECT messages.*, accounts1.username, accounts1.thumbname, accounts2.username, accounts2.thumbname 

FROM messages, accounts accounts1, accounts accounts2

WHERE messages.xfrom = [current user's ID passed into function]

AND messages.account = messages.xfrom

AND accounts1.ID = messages.xfrom

AND accounts2.ID = messages.xto

AND messages.threadid = [threadid]

ORDER BY sent ASC";

 

Just guessing on the query, but something like this.

[Helmet]

I just replaced my query with this based on your suggestion, and I know I'm closer, but now I getting a huge list of results even though there is only one message with the passed threadid:

$query = "SELECT
				messages.*, 
				images1.thumbname AS sender_thumbname, 
				accounts1.username AS sender_username, 
				images2.thumbname AS receiver_thumbname, 
				accounts2.username AS receiver_username 
			FROM
				messages, 
				images images1, 
				images images2, 
				accounts accounts1, 
				accounts accounts2
			WHERE
				messages.threadid = '$id'
			AND 
				images1.main = 1 
			AND 
				images2.main = 1 
			ORDER BY sent ASC";

 

Unfortunately due to an issue with a BB package I'm integrating, I have the thumbname in two tables, so that's why I'm using the images table now..

 

[Helmet]

This is a honkin' query, but I seem to have gotten it working. Please let me know if I can shorten this puppy, and thanks for pointing me in the right direction!! 

		$query = "SELECT
				messages.sent, messages.message, messages.subject, messages.xto, messages.threadid,
				images1.thumbname AS sender_thumbname,
				accounts1.username AS sender_username, 
				images2.thumbname AS receiver_thumbname,
				accounts2.username AS receiver_username  
			FROM
				messages, 
				images images1, 
				images images2, 
				accounts accounts1, 
				accounts accounts2
			WHERE
				messages.threadid = '$id'
		AND 
				images1.main = 1 
			AND 
				images2.main = 1 
			AND
				accounts2.ID = messages.xto
			AND
				images2.account = messages.xto
			AND
				images1.account = messages.xfrom
			AND
				accounts1.ID = messages.xfrom
			ORDER BY sent ASC";

 

 

[aschk]

A shorter version with ANSI joins and table aliases.

 

SELECT m.sent
      , m.message
      , m.subject
      , m.xto
      , m.threadid
      ,images1.thumbname AS sender_thumbname
      ,accounts1.username AS sender_username
      ,images2.thumbname AS receiver_thumbname
      ,accounts2.username AS receiver_username  
FROM messages m
JOIN images i1 ON i1.main = 1
JOIN images i2 ON i2.main = 1
JOIN accounts `to` ON `to`.ID = m.xto AND `to`.ID = m.xfrom
JOIN accounts `from` ON `from`.ID = m.xfrom
WHERE
messages.threadid = '$id'
ORDER BY sent ASC

 

You might want to test the above and make sure it works as expected.