By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,740 Members | 1,330 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,740 IT Pros & Developers. It's quick & easy.

Simply query of joined tables

P: n/a
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
Dec 8 '05 #1
Share this Question
Share on Google+
1 Reply

P: n/a
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
Dec 8 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.