Solved

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

Posted on 2011-02-28
17
397 Views
Last Modified: 2012-06-22
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? :(
0
Comment
Question by:intellisource
  • 7
  • 6
  • 2
  • +2
17 Comments
 
LVL 40

Expert Comment

by:Sharath
ID: 35004512
I did not get you. Could you post your result set with expected result set?
0
 
LVL 3

Accepted Solution

by:
ModuleKev earned 500 total points
ID: 35004969
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
 
LVL 3

Expert Comment

by:ModuleKev
ID: 35004990
Sorry IFNULL... I am mainly based on SQL Server.
0
 
LVL 3

Expert Comment

by:ModuleKev
ID: 35005001
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
 

Author Comment

by:intellisource
ID: 35005070
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
 
LVL 7

Expert Comment

by:Vimal DM
ID: 35005612
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
 

Author Comment

by:intellisource
ID: 35005738
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
 

Author Comment

by:intellisource
ID: 35005750
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 3

Expert Comment

by:ModuleKev
ID: 35005803
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
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 35006529
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
 

Author Closing Comment

by:intellisource
ID: 35006677
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
 
LVL 3

Expert Comment

by:ModuleKev
ID: 35006866
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
 

Author Comment

by:intellisource
ID: 35006952
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
 
LVL 3

Expert Comment

by:ModuleKev
ID: 35007017
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
 

Author Comment

by:intellisource
ID: 35007107
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
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 35007236
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
 

Author Comment

by:intellisource
ID: 35008322
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

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This article discusses how to create an extensible mechanism for linked drop downs.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now