BETWEEN query not working just right

Databases
1

Hey Guys,
I’ve got a time clock database and I’m trying to run a report of people who were late for a shift. I’m pulling data from two tables (data and users) I have two time fields. One is time (VARCHAR with AM/PM, etc.) and the other is trutime (DATETIME field).
Here’s the query:

SELECT data.date, data.time, data.trutime, data.reason, users.FirstName, users.LastName FROM data, users WHERE data.reason = 'morning_arrival' AND data.trutime BETWEEN '08:45:00' AND '10:00:00' ORDER BY data.date");

Here’s what I’m getting:

Overages Report for month of January
Name Date Time Reason
Delilah Brantley 2013-01-16 08:51:49 am morning_arrival
Lychelle Allen 2013-01-16 08:51:49 am morning_arrival
Chris Woodburn 2013-01-16 08:51:49 am morning_arrival
Ellen Jones 2013-01-16 08:51:49 am morning_arrival
Heath Lee 2013-01-16 08:51:49 am morning_arrival
Anne Bowen 2013-01-16 08:51:49 am morning_arrival
Josh Sheffield 2013-01-16 08:51:49 am morning_arrival
Lychelle Allen 2013-01-17 08:45:02 am morning_arrival
Chris Woodburn 2013-01-17 08:45:02 am morning_arrival
Ellen Jones 2013-01-17 08:45:02 am morning_arrival
Heath Lee 2013-01-17 08:45:02 am morning_arrival
Anne Bowen 2013-01-17 08:45:02 am morning_arrival
Josh Sheffield 2013-01-17 08:45:02 am morning_arrival
Delilah Brantley 2013-01-17 08:45:02 am morning_arrival

As you can see, it’s adding every user to every overage…I know it’s a simple something, but blast if I can see it.
Just for reference, here’s the php code to go with:

<?php
	

		//find overages	
		$query = mysql_query("SELECT data.date, data.time, data.trutime, data.reason, users.FirstName, users.LastName FROM data, users WHERE data.reason = 'morning_arrival' AND data.trutime BETWEEN '08:45:00' AND '10:00:00' ORDER BY data.date");
		$check = mysql_num_rows($query);
 		if ($check == 0) {
 			die($query."<br/><br/>".mysql_error());
 				}
		else {
		while ($result = mysql_fetch_array( $query ))   {
		
		$first=$result['FirstName'];
		$last=$result['LastName'];
		$name= $first . " " . $last;
		echo "<tr><td>";
		echo $name;
		echo "</td><td>";
		echo $result['date'];
		echo "</td><td>";
		echo $result['time'];
		echo "</td><td>";
		echo $result['reason'];
		echo "</td></tr>";
		
?>

Any help would be GREATLY appreciated!!!
Thanks in advance guys

2

You forgot the join criteria in your query. Right now it’s giving you a cartesian product.

3

something’s not quite right here because a DATETIME column includes a date component combined with a time component, yet your WHERE condition is

 data.trutime BETWEEN '08:45:00' AND '10:00:00'
4

Can you please explain a little further? This is obviously above my current skillset, but would love to learn this!

5

Sorry it’s a TIME column NOT a DATETIME…my mistake. Sorry for adding to the confusion.

6

When you query more than one table, you need to specify the relationship between the tables else you will have a cartesian product, that is, the result of the query will be that for every row of table A, it will show all the records from table B.

When there’s a relationship between two tables, you only get the rows of each table that have something in common.

Let me put an example:

You have a table customers

cust_ID    Name
  1             Me
  2             You

and a table where you information about their invoices (I’ll forget that there could be a table with the products just to keep it simple)

invoice_nbr   product        cust_ID
1                milk             2
2                chocolate     2
3                bread          1

as you can see, in both tables there’s a customer ID, there is a relation between them. If I don’t JOIN them (that is, I don’t add the relationship in my query), I will get 2 customers x 3 invoices = 6 records

But if I use a JOIN, I will only get the true number of records, 3 which is the number of invoices that it is really associated to a customer.

You can see the relationship when you see how they JOIN. He doesn’t have one.

7

Worked like a charm! Thanks! Now here’s another conundrum:

what about finding records where the time between lunch_leave and lunch_return is greater than 30 minutes? I don’t think I can run it all in one big query and be able to have it make sense on the output just yet. But is there a calculation methodology? I can see how to see if a time stamp is greater than a predetermined time, but we all leave for lunch at various times…so it’ll be comparing leaving and return. I guess I’ll need to do some maths as well?
Thanks in adavance…If i need to create another thread, let me know.

8

SELECT ( TIME_TO_SEC(end_lunch) - TIME_TO_SEC(start_lunch) ) / 60 AS lunch_minutes

9

man…I think I owe you a beer.