Greywacke Posted March 22, 2010 Share Posted March 22, 2010 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 More sharing options...
Greywacke Posted March 22, 2010 Author Share Posted March 22, 2010 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 More sharing options...
Greywacke Posted March 22, 2010 Author Share Posted March 22, 2010 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 More sharing options...
justsomeguy Posted March 22, 2010 Share Posted March 22, 2010 You can use an if structure in any select statement:http://dev.mysql.com/doc/refman/5.0/en/con...-functions.html Link to comment Share on other sites More sharing options...
Greywacke Posted March 23, 2010 Author Share Posted March 23, 2010 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 More sharing options...
Greywacke Posted March 23, 2010 Author Share Posted March 23, 2010 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 32all 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 More sharing options...
justsomeguy Posted March 23, 2010 Share Posted March 23, 2010 You have 2 WHERE clauses, you can only have one. Probably something like this:WHERE NOT EXISTS (...) AND ... Link to comment Share on other sites More sharing options...
Greywacke Posted March 24, 2010 Author Share Posted March 24, 2010 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 More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.