Simply query of joined tables

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
I have a simple query of joined tables that is failing to give me any rows
of data (though, in checking by hand, it certainly should). Essentially, I
am trying to return all rows from `ups` that have   `floattime`  not equal
to it's default value of '0' (please not, in this db dates and times are
saved as varchars - so essentially I am comparing strings)

SELECT,associates.branch ,associates.username ,ups.time
,ups.floattime FROM ups ,associates    WHERE
(  AND >='2005-12-05%' AND
<='2005-12-07%' AND ups.cxl <=0 AND ups.floattime <>'0'

The structure of the two tables is:

  `id` int(11) NOT NULL auto_increment,
  `date` varchar(16) default NULL,
  `time` varchar(11) default NULL,
  `associatekey` int(11) default NULL,
  `floattime` varchar(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)

CREATE TABLE `associates` (
  `id` int(11) NOT NULL auto_increment,
  `username` varchar(16) NOT NULL default '',
   `branch` varchar(30) default NULL,

My query returns an empty result set, which I have said is not right. Oddly,
if I eliminate the last phrase of the sql statement, "ups.floattime <>'0' "
it runs, returning all rows, but making the joins between the tables
correctly. Then, if I eliminate the need to join the tables (i.e. if I say I
dont want information about the associate) and distill my query down to

SELECT  date,time,floattime FROM ups     WHERE   date >='2005-12-05%' AND
date <='2005-12-07%'  AND floattime <>'0'

It runs correctly, returning the four correct rows.

2005-12-06 Tue  11:05:29  11:04:27
2005-12-07 Wed  13:59:14  13:58:23
2005-12-07 Wed  14:08:10  14:07:23
2005-12-07 Wed  14:13:30  14:12:33

So I am asking here, how can I construct this query, given the above table
structures, to return information about the associate as I have tried to do
in the initial query? What do I have wrong with that initial query that it
will not return the information I am looking for? TIA, Ike

Re: Simply query of joined tables

Ike wrote:
Quoted text here. Click to load it
What is ups.cxl?  You are using it in the WHERE clause but it
isn't in your CREATE TABLE statement.


Site Timeline