Simply query of joined tables

[Ike]

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 ups.date,associates.branch ,associates.username ,ups.time
,ups.floattime FROM ups ,associates WHERE
(ups.associatekey=associates.id) AND ups.date >='2005-12-05%' AND ups.date
<='2005-12-07%' AND ups.cxl <=0 AND ups.floattime <>'0'

The structure of the two tables is:

CREATE TABLE `ups` (
`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

[jerry gitomer]

Ike wrote:

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 ups.date,associates.branch ,associates.username ,ups.time
,ups.floattime FROM ups ,associates WHERE
(ups.associatekey=associates.id) AND ups.date >='2005-12-05%' AND ups.date
<='2005-12-07%' AND ups.cxl <=0 AND ups.floattime <>'0'

The structure of the two tables is:

CREATE TABLE `ups` (
`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

What is ups.cxl? You are using it in the WHERE clause but it
isn't in your CREATE TABLE statement.

Jerry