Complex "ORDER BY", pre-selected "SELECT AS" field: working pre-select and IFNULL() on one field into the SELECT JOIN

hi there,
the following sql statement needs to be primarily ordered by a "SELECT AS" clause for nonexistant records in the 34_lastleadsent table. The sql comment is just above and below where the records will not always exist. if they do exist - select them, otherwise select "0000-00-00 00:00:00" (a null date):
	$sql1 = "SELECT 
			5_suppliers.text_ContactFirstName, 
			5_suppliers.text_ContactSurname, 
			5_suppliers.text_SupplierName, 
			5_suppliers.`text_ContactE-mail`, 
			5_suppliers.bigint_CurrentBalance, 
			5_suppliers.bigint_ContactTel, 
			5_suppliers.text_SupplierAddress, 
			5_suppliers.bigint_SupplierID, 
			5_suppliers.text_GoogleMapsURL, 
			5_suppliers.text_VTigerData, 
			5_suppliers.tinyint_VariablePricingEnabled, 
			5_suppliers.smallint_SupplierStatus, 
			# if non-existant select \"0000-00-00 00:00:00\":
			34_lastleadsent.timestamp_LastLeadSentDate, 
			# end if non-existant
			5_suppliers.text_AccMgr, 
			5_suppliers.`text_AccMgrE-mail`, 
			5_suppliers.bigint_CreditInvoice 
		FROM 
			5_suppliers 
		LEFT JOIN (
			4_servicesuppliers, 
			34_lastleadsent
		) 
		ON (
			4_servicesuppliers.bigint_SupplierID = 5_suppliers.bigint_SupplierID AND 
			34_lastleadsent.bigint_SupplierID = 5_suppliers.bigint_SupplierID AND 
			34_lastleadsent.bigint_ZipCode = ".$GLOBALS["region"]." 
		) 
		WHERE 
			CURRENT_TIMESTAMP >= 5_suppliers.timestamp_DateStart AND 
			CURRENT_TIMESTAMP <= 5_suppliers.timestamp_DateEnd AND 
			5_suppliers.smallint_SupplierStatus = ".$status." AND 
			5_supppliers.bigint_ServiceID = ".$sid." AND 
			".(
				(is_array($regions))?
				"(".implode(" OR ",$regions).")":
				"4_servicesuppliers.bigint_RegionID = ".$GLOBALS["region"]
			)." AND 
			34_lastleadsent.bigint_ZipCode = ".$GLOBALS["region"]." 
		ORDER BY 
			34_lastleadsent.timestamp_LastLeadSentDate ASC, 
			5_suppliers.bigint_SupplierID ASC;";

Open in new window

here is apparently how to pre-select in order to use the WHERE and ORDER BY clauses on a non-existing field over at the TekTips Forums:
SELECT test
  FROM ( SELECT field1 AS test
           FROM tbl_test ) AS d
 WHERE test > 5;
i am having a bit of trouble working the pre-select into this mysql select join statement though, anybody skilled enough to assist me in the right direction? :(
intellisourceAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
ModuleKevConnect With a Mentor Commented:
Basic syntax would be:

SELECT ISNULL(34_lastleadsent.timestamp_LastLeadSentDate, "0000-00-00 00:00:00")
FROM table
ORDER BY
ISNULL(34_lastleadsent.timestamp_LastLeadSentDate, "0000-00-00 00:00:00") ASC
0
 
SharathData EngineerCommented:
I did not get you. Could you post your result set with expected result set?
0
 
ModuleKevCommented:
Sorry IFNULL... I am mainly based on SQL Server.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
ModuleKevCommented:
Or you could use a CASE statement, but IFNULL would prob suit best.

http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html
0
 
intellisourceAuthor Commented:
hi certainly...
the current selection shows the following:
text_ContactFirstName  text_ContactSurname  text_SupplierName  text_ContactE-mail               bigint_CurrentBalance  bigint_ContactTel  text_SupplierAddress                                    bigint_SupplierID  text_GoogleMapsURL                                                                                                                                                                                                                                               text_VTigerData  tinyint_VariablePricingEnabled  timestamp_DateStart  timestamp_DateEnd    smallint_SupplierStatus  bigint_ServiceID  bigint_RegionID  timestamp_LastLeadSentDate  text_AccMgr  text_AccMgrE-mail         bigint_CreditInvoice
Gloria                 Vaughan              Test Supplier 01   pierre@canopyxchange.za.net	10000                  12-330-1323        204 S DIAGONAL ST APT C\nDECHERD, TENNESSEE 37324-3198  1                  http://maps.google.com/maps?f=q&source=s_q&hl=en&geocode=&q=204 S DIAGONAL ST APT C DECHERD, TENNESSEE 37324-3198 United States&aq=&sll=-25.700763,28.207846&sspn=0.01628,0.033023&ie=UTF8&hq=&hnear=204 S Diagonal St, Decherd, Franklin, Tennessee 37324&z=17                   0                               2011-01-01 01:00:00  2012-01-01 00:59:00  0                        1                 100              NULL                        Jaap Venter  jaap.venter@ananzi.co.za  10000
Gloria                 Vaughan              Test Supplier 02   pierre@canopyxchange.za.net	10000                  12-330-1323        204 S DIAGONAL ST APT C\nDECHERD, TENNESSEE 37324-3198  2                  http://maps.google.com/maps?f=q&source=s_q&hl=en&geocode=&q=204 S DIAGONAL ST APT C DECHERD, TENNESSEE 37324-3198 United States&aq=&sll=-25.700763,28.207846&sspn=0.01628,0.033023&ie=UTF8&hq=&hnear=204 S Diagonal St, Decherd, Franklin, Tennessee 37324&z=17                   0                               2011-01-01 01:00:00  2012-01-01 00:59:00  0                        1                 2                NULL                        Jaap Venter  jaap.venter@ananzi.co.za  10000
Gloria                 Vaughan              Test Supplier 03   pierre@canopyxchange.za.net	10000                  72-915-4799        204 S DIAGONAL ST APT C\nDECHERD, TENNESSEE 37324-3198  3                  http://maps.google.com/maps?f=q&source=s_q&hl=en&geocode=&q=204 S DIAGONAL ST APT C DECHERD, TENNESSEE 37324-3198 United States&aq=&sll=-25.700763,28.207846&sspn=0.01628,0.033023&ie=UTF8&hq=&hnear=204 S Diagonal St, Decherd, Franklin, Tennessee 37324&z=17                   0                               2011-01-01 01:00:00  2012-01-01 00:59:00  0                        1                 102020           NULL                        Jaap Venter  jaap.venter@ananzi.co.za  10000
Gloria                 Vaughan              Test Supplier 04   pierre@canopyxchange.za.net	10000                  72-915-4799        204 S DIAGONAL ST APT C\nDECHERD, TENNESSEE 37324-3198  4                  http://maps.google.com/maps?f=q&source=s_q&hl=en&geocode=&q=204 S DIAGONAL ST APT C DECHERD, TENNESSEE 37324-3198 United States&aq=&sll=-25.700763,28.207846&sspn=0.01628,0.033023&ie=UTF8&hq=&hnear=204 S Diagonal St, Decherd, Franklin, Tennessee 37324&z=17                   0                               2011-01-01 01:00:00  2012-01-01 00:59:00  0                        1                 99501            NULL                        Jaap Venter  jaap.venter@ananzi.co.za  10000

Open in new window

but the selection needs to show the following at the moment: when each lead is sent a record gets updated and inserted if nonexistant in the 34_lastleadsent table for each supplier, per zipcode.
text_ContactFirstName  text_ContactSurname  text_SupplierName  text_ContactE-mail               bigint_CurrentBalance  bigint_ContactTel  text_SupplierAddress                                    bigint_SupplierID  text_GoogleMapsURL                                                                                                                                                                                                                                               text_VTigerData  tinyint_VariablePricingEnabled  timestamp_DateStart  timestamp_DateEnd    smallint_SupplierStatus  bigint_ServiceID  bigint_RegionID  timestamp_LastLeadSentDate  text_AccMgr  text_AccMgrE-mail         bigint_CreditInvoice
Gloria                 Vaughan              Test Supplier 01   pierre@canopyxchange.za.net	10000                  12-330-1323        204 S DIAGONAL ST APT C\nDECHERD, TENNESSEE 37324-3198  1                  http://maps.google.com/maps?f=q&source=s_q&hl=en&geocode=&q=204 S DIAGONAL ST APT C DECHERD, TENNESSEE 37324-3198 United States&aq=&sll=-25.700763,28.207846&sspn=0.01628,0.033023&ie=UTF8&hq=&hnear=204 S Diagonal St, Decherd, Franklin, Tennessee 37324&z=17                   0                               2011-01-01 01:00:00  2012-01-01 00:59:00  0                        1                 100              0000-00-00 00:00:00         Jaap Venter  jaap.venter@ananzi.co.za  10000
Gloria                 Vaughan              Test Supplier 02   pierre@canopyxchange.za.net	10000                  12-330-1323        204 S DIAGONAL ST APT C\nDECHERD, TENNESSEE 37324-3198  2                  http://maps.google.com/maps?f=q&source=s_q&hl=en&geocode=&q=204 S DIAGONAL ST APT C DECHERD, TENNESSEE 37324-3198 United States&aq=&sll=-25.700763,28.207846&sspn=0.01628,0.033023&ie=UTF8&hq=&hnear=204 S Diagonal St, Decherd, Franklin, Tennessee 37324&z=17                   0                               2011-01-01 01:00:00  2012-01-01 00:59:00  0                        1                 2                0000-00-00 00:00:00         Jaap Venter  jaap.venter@ananzi.co.za  10000
Gloria                 Vaughan              Test Supplier 03   pierre@canopyxchange.za.net	10000                  72-915-4799        204 S DIAGONAL ST APT C\nDECHERD, TENNESSEE 37324-3198  3                  http://maps.google.com/maps?f=q&source=s_q&hl=en&geocode=&q=204 S DIAGONAL ST APT C DECHERD, TENNESSEE 37324-3198 United States&aq=&sll=-25.700763,28.207846&sspn=0.01628,0.033023&ie=UTF8&hq=&hnear=204 S Diagonal St, Decherd, Franklin, Tennessee 37324&z=17                   0                               2011-01-01 01:00:00  2012-01-01 00:59:00  0                        1                 102020           0000-00-00 00:00:00         Jaap Venter  jaap.venter@ananzi.co.za  10000
Gloria                 Vaughan              Test Supplier 04   pierre@canopyxchange.za.net	10000                  72-915-4799        204 S DIAGONAL ST APT C\nDECHERD, TENNESSEE 37324-3198  4                  http://maps.google.com/maps?f=q&source=s_q&hl=en&geocode=&q=204 S DIAGONAL ST APT C DECHERD, TENNESSEE 37324-3198 United States&aq=&sll=-25.700763,28.207846&sspn=0.01628,0.033023&ie=UTF8&hq=&hnear=204 S Diagonal St, Decherd, Franklin, Tennessee 37324&z=17                   0                               2011-01-01 01:00:00  2012-01-01 00:59:00  0                        1                 99501            0000-00-00 00:00:00         Jaap Venter  jaap.venter@ananzi.co.za  10000

Open in new window

please note the timestamp_LastLeadSentDate field, currently selecting NULL. it needs to primarily order by this, selecting 0000-00-00 00:00:00 if no records were found in the 34_lastleadsent table. now i know that ordering by a SELECT AS cannot easily be done - not unless you do a pre-select as in the example i showed.
current timestamp_LastLeadSentDate:
NULL
NULL
NULL
NULL

Open in new window

wanted timestamp_LastLeadSentDate:
0000-00-00 00:00:00
0000-00-00 00:00:00
0000-00-00 00:00:00
0000-00-00 00:00:00

Open in new window

eventually the fields will be populated in that table as sales leads get sent out through the zipcodes in the United States.
hope this gives some clarity on what needs to be done?
0
 
Vimal DMSenior Software EngineerCommented:
Hai,

If that mysql database field has the datatype of "timestamp",need not to worry about the timestamp display format

If there is not date o the database,the timestamp datatype will have the format that you have asked for.

Please check,is that my understanding
0
 
intellisourceAuthor Commented:
hi ModuleKev,
unfortunately implementing it into the sql select statement, returns NO results, the sql is implemented as below:
SELECT 
	5_suppliers.text_ContactFirstName, 
	5_suppliers.text_ContactSurname, 
	5_suppliers.text_SupplierName, 
	5_suppliers.`text_ContactE-mail`, 
	5_suppliers.bigint_CurrentBalance, 
	5_suppliers.bigint_ContactTel, 
	5_suppliers.text_SupplierAddress, 
	5_suppliers.bigint_SupplierID, 
	5_suppliers.text_GoogleMapsURL, 
	5_suppliers.text_VTigerData, 
	5_suppliers.tinyint_VariablePricingEnabled, 
	5_suppliers.smallint_SupplierStatus, 
	5_suppliers.text_AccMgr, 
	5_suppliers.`text_AccMgrE-mail`, 
	5_suppliers.bigint_CreditInvoice, 
	5_suppliers.timestamp_DateStart, 
	5_suppliers.timestamp_DateEnd, 
	4_servicesuppliers.bigint_ServiceID, 
	4_servicesuppliers.bigint_RegionID, 
	IFNULL(34_lastleadsent.timestamp_LastLeadSentDate, "0000-00-00 00:00:00")
FROM 
	5_suppliers 
LEFT JOIN (
	4_servicesuppliers, 
	34_lastleadsent
) 
ON (
	5_suppliers.bigint_SupplierID = 4_servicesuppliers.bigint_SupplierID AND 
	5_suppliers.bigint_SupplierID = 34_lastleadsent.bigint_SupplierID
) 
WHERE 
	CURRENT_TIMESTAMP >= 5_suppliers.timestamp_DateStart AND 
	CURRENT_TIMESTAMP <= 5_suppliers.timestamp_DateEnd AND 
	5_suppliers.smallint_SupplierStatus = 0 AND 
	4_servicesuppliers.bigint_ServiceID = 1 AND 
	(
		4_servicesuppliers.bigint_RegionID = 100 OR    # country = United States of America
		4_servicesuppliers.bigint_RegionID = 2 OR      # state = Alaska
		4_servicesuppliers.bigint_RegionID = 102020 OR # county = Anchorage
		4_servicesuppliers.bigint_RegionID = 99501     # city, zip = Anchorage, 99501
	)
ORDER BY 
	IFNULL(34_lastleadsent.timestamp_LastLeadSentDate, "0000-00-00 00:00:00") ASC, 
	5_suppliers.bigint_SupplierID ASC;

Open in new window

0
 
intellisourceAuthor Commented:
vimalmaria, i am afraid you do not understand my question. read my reply to Sharath_123 please - i apologise for omitting that it was in reply to his post.
0
 
ModuleKevCommented:
That's a little strange. Did notice that your original query is different to what you just posted. Previousily you'd included bigint_ZipCode...

SELECT 
	5_suppliers.text_ContactFirstName, 
	5_suppliers.text_ContactSurname, 
	5_suppliers.text_SupplierName, 
	5_suppliers.`text_ContactE-mail`, 
	5_suppliers.bigint_CurrentBalance, 
	5_suppliers.bigint_ContactTel, 
	5_suppliers.text_SupplierAddress, 
	5_suppliers.bigint_SupplierID, 
	5_suppliers.text_GoogleMapsURL, 
	5_suppliers.text_VTigerData, 
	5_suppliers.tinyint_VariablePricingEnabled, 
	5_suppliers.smallint_SupplierStatus, 
	5_suppliers.text_AccMgr, 
	5_suppliers.`text_AccMgrE-mail`, 
	5_suppliers.bigint_CreditInvoice, 
	5_suppliers.timestamp_DateStart, 
	5_suppliers.timestamp_DateEnd, 
	4_servicesuppliers.bigint_ServiceID, 
	4_servicesuppliers.bigint_RegionID, 
	IFNULL(34_lastleadsent.timestamp_LastLeadSentDate, "0000-00-00 00:00:00") AS timestamp_LastLeadSentDate
FROM 
	5_suppliers 
LEFT JOIN (
	4_servicesuppliers, 
	34_lastleadsent
) 
ON (
	5_suppliers.bigint_SupplierID = 4_servicesuppliers.bigint_SupplierID AND 
	5_suppliers.bigint_SupplierID = 34_lastleadsent.bigint_SupplierID 
	AND 34_lastleadsent.bigint_ZipCode = [INSERTVALUE]
) 
WHERE 
	CURRENT_TIMESTAMP >= 5_suppliers.timestamp_DateStart AND 
	CURRENT_TIMESTAMP <= 5_suppliers.timestamp_DateEnd AND 
	5_suppliers.smallint_SupplierStatus = 0 AND 
	4_servicesuppliers.bigint_ServiceID = 1 AND 
	(
		4_servicesuppliers.bigint_RegionID = 100 OR    # country = United States of America
		4_servicesuppliers.bigint_RegionID = 2 OR      # state = Alaska
		4_servicesuppliers.bigint_RegionID = 102020 OR # county = Anchorage
		4_servicesuppliers.bigint_RegionID = 99501     # city, zip = Anchorage, 99501
	) AND 34_lastleadsent.bigint_ZipCode = [INSERTVALUE]
ORDER BY 
	IFNULL(34_lastleadsent.timestamp_LastLeadSentDate, "0000-00-00 00:00:00") ASC, 
	5_suppliers.bigint_SupplierID ASC; 

Open in new window


Can you try something simpiler:
SELECT
	34_lastleadsent.timestamp_LastLeadSentDate AS Prevtimestamp_LastLeadSentDate,
	IFNULL(34_lastleadsent.timestamp_LastLeadSentDate, "0000-00-00 00:00:00") AS timestamp_LastLeadSentDate
FROM 	34_lastleadsent
ORDER BY 
	IFNULL(34_lastleadsent.timestamp_LastLeadSentDate, "0000-00-00 00:00:00") ASC,

Open in new window


Also see:
http://onlamp.com/pub/a/onlamp/excerpt/mysqlckbk/index1.html
0
 
Ray PaseurCommented:
The column named "timestamp_LastLeadSentDate" should be defined like this:

timestamp_LastLeadSentDate DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00'

You might find that the blank in the default definition really needs to be a 'T' (not sure about that).
0
 
intellisourceAuthor Commented:
hi ModuleKev,
it returns a recordset if i comment out the ON () part for the 34_lastleadsent table. if not, then i get nothing :( how could i work around this? because if there are records for zipcode and lastsentlead date values, per supplier - it should select THAT last sent lead date and not 0.
sql that selects correct data - with slight outstanding want, mentioned above:
[code]SELECT
      5_suppliers.text_ContactFirstName,
      5_suppliers.text_ContactSurname,
      5_suppliers.text_SupplierName,
      5_suppliers.`text_ContactE-mail`,
      5_suppliers.bigint_CurrentBalance,
      5_suppliers.bigint_ContactTel,
      5_suppliers.text_SupplierAddress,
      5_suppliers.bigint_SupplierID,
      5_suppliers.text_GoogleMapsURL,
      5_suppliers.text_VTigerData,
      5_suppliers.tinyint_VariablePricingEnabled,
      5_suppliers.timestamp_DateStart,
      5_suppliers.timestamp_DateEnd,
      5_suppliers.smallint_SupplierStatus,
      4_servicesuppliers.bigint_ServiceID,
      4_servicesuppliers.bigint_RegionID,
      # if non-existant select 0000-00-00 00:00:00
      IFNULL(34_lastleadsent.timestamp_LastLeadSentDate, "0000-00-00 00:00:00"),
      # end if non-existant
      5_suppliers.text_AccMgr,
      5_suppliers.`text_AccMgrE-mail`,
      5_suppliers.bigint_CreditInvoice
FROM
      5_suppliers
LEFT JOIN (
      4_servicesuppliers,
      34_lastleadsent
) ON (
      4_servicesuppliers.bigint_SupplierID = 5_suppliers.bigint_SupplierID #AND
      #34_lastleadsent.bigint_SupplierID = 5_suppliers.bigint_SupplierID AND
      #34_lastleadsent.bigint_ZipCode = 99501
) WHERE
      CURRENT_TIMESTAMP >= 5_suppliers.timestamp_DateStart AND
      CURRENT_TIMESTAMP <= 5_suppliers.timestamp_DateEnd AND
      5_suppliers.smallint_SupplierStatus = 0 AND
      4_servicesuppliers.bigint_ServiceID = 1 AND
      (
            4_servicesuppliers.bigint_RegionID = 100 OR    # country = United States of America
            4_servicesuppliers.bigint_RegionID = 2 OR      # state = Alaska
            4_servicesuppliers.bigint_RegionID = 102020 OR # county = Anchorage
            4_servicesuppliers.bigint_RegionID = 99501     # city, zip = Anchorage, 99501
      )
ORDER BY
      IFNULL(34_lastleadsent.timestamp_LastLeadSentDate, "0000-00-00 00:00:00") ASC,
      5_suppliers.bigint_SupplierID ASC;[/code]
0
 
ModuleKevCommented:
You need to break it down a bit. I've never been a fan of the syntax above. By taking out the ON clause, you're leaving it to the where clause statement to resolve your query, which is fine, if you know what you're doing.

Try this way, it may help you understand what you're trying to accomplish:

SELECT *
FROM tableA A
   LEFT JOIN tableB B
      ON A.Id = B.ParentId
   LEFT JOIN tableC C
      ON B.Id = C.ParentId

Open in new window


So yours would looks like

SELECT *
FROM 5_suppliers S
   LEFT JOIN 4_servicesuppliers SS
      ON S.bigint_SupplierID = SS.bigint_SupplierID
   LEFT JOIN 34_lastleadsent LLS
      ON S.bigint_SupplierID = LLS.bigint_SupplierID
            AND LLS.bigint_ZipCode = 99501
WHERE 
   CURRENT_TIMESTAMP >= S.timestamp_DateStart AND 
   CURRENT_TIMESTAMP <= S.timestamp_DateEnd AND 
   S.smallint_SupplierStatus = 0 AND 
   SS.bigint_ServiceID = 1 AND 
      (
            SS.bigint_RegionID = 100 OR    # country = United States of America
            SS.bigint_RegionID = 2 OR      # state = Alaska
            SS.bigint_RegionID = 102020 OR # county = Anchorage
            SS.bigint_RegionID = 99501     # city, zip = Anchorage, 99501
      )

Open in new window


First thing I would do, is check your referencing the tables correctly. Are you sure it's a left join to 4_servicesuppliers? Looks more like it could be a normal JOIN. If you're still stuck, really it's a new question. Detail table structures, and what each table holds - not asking for real data.

Something is telling me that you don't have any criteria that matches in the  4_servicesuppliers.

SELECT *
FROM 5_suppliers
WHERE CURRENT_TIMESTAMP >= 5_suppliers.timestamp_DateStart AND
      CURRENT_TIMESTAMP <= 5_suppliers.timestamp_DateEnd AND
      5_suppliers.smallint_SupplierStatus = 0

Now go see what data you've got in 4_servicesuppliers
0
 
intellisourceAuthor Commented:
yes after Sharath_123 posted, i had to write a post to give more clarity on what needs to be done. sofar you have been the most helpful, ModuleKev - but unfortunately not to a complete solution yet. how would i fix this? re - last post.
0
 
ModuleKevCommented:
If you don't get any data from the following, either it's correct or you need to change what you're looking for.

SELECT *
FROM 5_suppliers S
      INNER JOIN 4_servicesuppliers SS
            ON S.bigint_SupplierID = SS.bigint_SupplierID
WHERE CURRENT_TIMESTAMP >= S.timestamp_DateStart AND 
      CURRENT_TIMESTAMP <= S.timestamp_DateEnd AND 
      S.smallint_SupplierStatus = 0 AND 
      SS.bigint_ServiceID = 1 AND 
      (
            SS.bigint_RegionID = 100 OR    # country = United States of America
            SS.bigint_RegionID = 2 OR      # state = Alaska
            SS.bigint_RegionID = 102020 OR # county = Anchorage
            SS.bigint_RegionID = 99501     # city, zip = Anchorage, 99501
      ) 

Open in new window

0
 
intellisourceAuthor Commented:
and no Ray_Paseur, am not creating a table - the table already exists, as a timestamp for that field yes, and bigint for zip and supplier id.
thanks a mill ModuleKev! :D
the following code WORKS beautifully! :D
SELECT *
FROM 5_suppliers S
   LEFT JOIN 4_servicesuppliers SS
      ON S.bigint_SupplierID = SS.bigint_SupplierID
   LEFT JOIN 34_lastleadsent LLS
      ON S.bigint_SupplierID = LLS.bigint_SupplierID
            AND LLS.bigint_ZipCode = 99501
WHERE 
   CURRENT_TIMESTAMP >= S.timestamp_DateStart AND 
   CURRENT_TIMESTAMP <= S.timestamp_DateEnd AND 
   S.smallint_SupplierStatus = 0 AND 
   SS.bigint_ServiceID = 1 AND 
      (
            SS.bigint_RegionID = 100 OR    # country = United States of America
            SS.bigint_RegionID = 2 OR      # state = Alaska
            SS.bigint_RegionID = 102020 OR # county = Anchorage
            SS.bigint_RegionID = 99501     # city, zip = Anchorage, 99501
      )
ORDER BY 
	LLS.timestamp_LastLeadSentDate ASC, 
	S.bigint_SupplierID ASC;

Open in new window

0
 
Ray PaseurCommented:
Wasn't asking you if you were creating a table, I was just suggesting that if you have defined the column the way I showed, you would not have the confusion about using the IFNULL control flow.  If the table already exists with a column definition that doesn't work the way you want, you can just use ALTER TABLE and add a column with a reasonable definition.  Easy!
0
 
intellisourceAuthor Commented:
sighs... it selected in pma, but did not in the php document...
on order - does NULL come before any date, i suspect it does because 0 < 1.
0
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.

All Courses

From novice to tech pro — start learning today.