Jump to content

selecting message id (by next message number) from table a where table b contains records of messages sent to suppliers.


Greywacke

Recommended Posts

hi there,sitting with a bit of a mental block atm, i need to select the message id (by next message number) from table a where table b contains records of messages sent to suppliers.how would i go about this?something like the following perhaps?

SELECT 12_prospectmessages.bigint_MessageID, 12_prospectmessages.smallint_ProspectMessageCount FROM 13_prospectleadsent, 12_prospectmessages WHERE 13_prospectleadsent.bigint_MessageID = 12_prospectmessages.bigint_MessageID AND 13_prospectleadsent.bigint_SupplierID = 5 ORDER BY 12_prospectmessages.smallint_ProspectMessageCount DESC

if no records are found in table b then the first message number's id should be selected. how would i go about this sql select statement? something tells me that the above attempt will not work.

Link to comment
Share on other sites

in the smallint_ProspectMessageCount field, on the 12_prospectmessages table, there are messages with number 1-5 and 99 for if the last message sent was smallint_ProspectMessageCount != 99 and existing.

Link to comment
Share on other sites

okay after some thinking, i've come up with the following sql statement... the only question is how would i be able to include an if statement in the select subquery?

SELECT 12_prospectmessages.bigint_MessageID FROM 12_prospectmessages WHERE 12_prospectmessages.smallint_ProspectMessageCount = (SELECT (1 + 12_prospectmessages.smallint_ProspectMessageCount) AS newid FROM 13_prospectleadsent WHERE 13_prospectleadsent.bigint_SupplierID = 5 ORDER BY 13_prospectleadsent.smallint_ProspectMessageCount DESC LIMIT 1)

this query unfortunately returns a blank recordset in PMA... how would i be able to do such a selection? if smallint_ProspectMessageCount < 99 and exists give the relevant bigint_MessageID, else give smallint_ProspectMessageCount = 99's bigint_MessageID...please help here, i seem to be totally stumped!

Link to comment
Share on other sites

okay here is revision 3 of the sql code...

SELECT 12_prospectmessages.bigint_MessageID FROM 12_prospectmessages WHERE 12_prospectmessages.smallint_ProspectMessageCount = (	SELECT (1 + 12_prospectmessages.smallint_ProspectMessageCount) 	AS newid 	FROM 12_prospectmessages, 13_prospectleadsent 	WHERE 		13_prospectleadsent.bigint_MessageID = 12_prospectmessages.bigint_MessageID AND 		13_prospectleadsent.bigint_SupplierID = 17 	ORDER BY 12_prospectmessages.smallint_ProspectMessageCount DESC 	LIMIT 1)

this works for selecting the next one for smallint_ProspectMessageCount up to 4, but 5 or 99 should return the id of smallint_ProspectMessageCount = 99.how do i implement an IF ELSE into a mysql statement? i believe it should be possible... another IF ELSE might be required should 1 + NULL != 1. logic dictates it should however... back to research when i wake up :)

Link to comment
Share on other sites

awesome!!! :)i've started compiling the following statement, and tested it in PMA to implement the requirements stated above:

SELECT IFNULL(	(		SELECT 12_prospectmessages.bigint_MessageID 		FROM 12_prospectmessages 		WHERE 12_prospectmessages.smallint_ProspectMessageCount = 		(			SELECT (1 + 12_prospectmessages.smallint_ProspectMessageCount) 			AS newid 			FROM 12_prospectmessages, 13_prospectleadsent 			WHERE 			13_prospectleadsent.bigint_MessageID = 12_prospectmessages.bigint_MessageID AND 			13_prospectleadsent.bigint_SupplierID = 17 			ORDER BY 12_prospectmessages.smallint_ProspectMessageCount DESC 			LIMIT 1		)	)	, (		SELECT IFNULL(			(				SELECT 12_prospectmessages.bigint_MessageID 				FROM 12_prospectmessages 				WHERE 12_prospectmessages.smallint_ProspectMessageCount = 99 AND 				12_prospectmessages.bigint_ServiceID = 1 				ORDER BY 12_prospectmessages.smallint_ProspectMessageCount ASC LIMIT 1			)			, 			(				SELECT 12_prospectmessages.bigint_MessageID 				FROM 12_prospectmessages 				WHERE 12_prospectmessages.smallint_ProspectMessageCount = 1 AND 				12_prospectmessages.bigint_ServiceID = 1 AND 				WHERE NOT EXISTS (SELECT * FROM 13_prospectleadsent WHERE 13_prospectleadsent.bigint_SupplierID = 17) 				ORDER BY 12_prospectmessages.smallint_ProspectMessageCount ASC LIMIT 1			)		)	));

something is not working with the WHERE NOT EXISTS clause. the error i receive is the following:

SELECT IFNULL( ( SELECT 12_prospectmessages.bigint_MessageID FROM 12_prospectmessages WHERE 12_prospectmessages.smallint_ProspectMessageCount = ( SELECT (1 + 12_prospectmessages.smallint_ProspectMessageCount) AS newid FROM 12_prospectmessages, 13_prospectleadsent WHERE 13_prospectleadsent.bigint_MessageID = 12_prospectmessages.bigint_MessageID AND 13_prospectleadsent.bigint_SupplierID = 17 ORDER BY 12_prospectmessages.smallint_ProspectMessageCount DESC LIMIT 1 ) ) , ( SELECT IFNULL( ( SELECT 12_prospectmessages.bigint_MessageID FROM 12_prospectmessages WHERE 12_prospectmessages.smallint_ProspectMessageCount = 99 AND 12_prospectmessages.bigint_ServiceID = 1 ORDER BY 12_prospectmessages.smallint_ProspectMessageCount ASC LIMIT 1 ) , ( SELECT 12_prospectmessages.bigint_MessageID FROM 12_prospectmessages WHERE 12_prospectmessages.smallint_ProspectMessageCount = 1 [...]MySQL said: Documentation#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE NOT EXISTS (SELECT * FROM 13_prospectleadsent WHERE 13_prospectleadsent.bi' at line 32
all the brackets are closing and correct (i tested this with Notepad++, which has syntax highlighting and tabbing/bracket binding aswell. i am struggling to see this forest for the trees...
Link to comment
Share on other sites

thanks justsomeguy,for steering me back on track :)i noticed yesterday that the problem was something to do with the WHERE clause, but i was a bit flabbergasted to say the least... :)so let me restart and run over the requirements...

1) i firstly need to select the next message id's that have been sent to the specific supplier for service A. 2). if subquery 1 returns NULL, i need to check for the next message id, say there are messages 1-7 or 1-10 or 1-5 or whatever exists in the messages table, for service A. 3) if subquery 2 returns NULL, then it retrieves the message id of message number 99.
so basically i will need the two nested IFNULL()'s, and 3 subqueries... here is what i came up with:
SELECT IFNULL(	(		SELECT 12_prospectmessages.bigint_MessageID 		FROM 12_prospectmessages 		WHERE 12_prospectmessages.smallint_ProspectMessageCount = 		(			SELECT (1 + 12_prospectmessages.smallint_ProspectMessageCount) 			AS newid 			FROM 12_prospectmessages, 13_prospectleadsent 			WHERE 			13_prospectleadsent.bigint_MessageID = 				12_prospectmessages.bigint_MessageID AND 			13_prospectleadsent.bigint_SupplierID = 20 AND 			12_prospectmessages.bigint_ServiceID = 1 			ORDER BY 			12_prospectmessages.smallint_ProspectMessageCount DESC 			LIMIT 1		)	)    , 	(		SELECT IFNULL( 			(				SELECT 12_prospectmessages.bigint_MessageID 				FROM 12_prospectmessages 				WHERE NOT EXISTS (					SELECT * FROM 13_prospectleadsent, 12_prospectmessages  					WHERE 					13_prospectleadsent.bigint_MessageID = 						12_prospectmessages.bigint_MessageID AND 					13_prospectleadsent.bigint_SupplierID = 20 AND 					12_prospectmessages.bigint_ServiceID = 1				) AND 				12_prospectmessages.smallint_ProspectMessageCount = 1 AND 				12_prospectmessages.bigint_ServiceID = 1 				ORDER BY 				12_prospectmessages.smallint_ProspectMessageCount ASC 				LIMIT 1			)			, 			(				SELECT 12_prospectmessages.bigint_MessageID 				FROM 12_prospectmessages 				WHERE 				12_prospectmessages.smallint_ProspectMessageCount = 99 AND 				12_prospectmessages.bigint_ServiceID = 1 				ORDER BY 				12_prospectmessages.smallint_ProspectMessageCount ASC 				LIMIT 1			)		)	)) AS bigint_MessageID;

i tested this on PMA in all 3 instances: an existing prospecting supplier, a new prospecting supplier, and a prospecting supplier that has received all the messages before.this issue has been resolved!!! :)

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...