01-25-2007, 09:36 AM
I'm having difficulties writing a SQL query that checks for information on a different table.
I'm sure the query is pretty easy using join (or so I think), but I just can't figure out how to use join (I never fully understood JOIN :( ).
I wrote a small example for my tables + the expected result. It can be found here (http://img207.imageshack.us/my.php?image=mysqlquery3gw.jpg). Can someone please explain what I'm missing?
Thanks a lot.
01-25-2007, 12:55 PM
SELECT tbl1.id, tbl2.mytime FROM tbl1 LEFT JOIN tbl2 ON tbl1.id = tbl2.tbl1id WHERE tbl1.status = 1 AND tbl2.type = 1 ORDER BY tbl1.id ASC
01-25-2007, 02:02 PM
I'm getting an empty query. Are you sure about this?
01-25-2007, 03:13 PM
since you are asking an SQL question then it either belongs in the mysql forum or if not using that database, then in the general database forum. In neither case should you post it in PHP forum. your issue is sql related. Please post in the appropriate forums.
01-25-2007, 03:16 PM
I never fully understood JOIN
Here (http://guelphdad.wefixtech.co.uk/sqlhelp/joins_basic.shtml) is an article that might help you understand joins. It is pretty basic.
01-25-2007, 03:17 PM
You might also want to explain the logic of your expected results. they are not clear from your example as to why those rows are the ones returned.
01-25-2007, 05:22 PM
Here's the logic in what I'm trying to do:
- STATUS (still hired, yes/no)
- TYPE (in/out of the office)
I want to display all employees that now working (type) with the time (report_time) they started to work.
Or to display all employees, with some note if the employee now working.
* Is it possible to move this thread to MySQL forum?
01-25-2007, 06:10 PM
so why does employee 1 show up with a time of 08:00? is that based on it being the most recent entry in the table for that employee? Most recent being determined by the id column in table 2?
01-25-2007, 07:10 PM
Sorry, I should have clarify this: REPORT_TIME (mytime in the picture) is in 10-digits format, that means year+month+day+time.
01-26-2007, 01:39 AM
and are you actually using a DATETIME or TIMESTAMP field to hold those values?
and again, to clarify, since you didn't directly answer, do you want the latest date then for each employee?
01-26-2007, 09:30 PM
I'm using PHP's time() function. What's the difference?
do you want the latest date then for each employee?
I want to select only employees who's Reports.TYPE is 1 and they're REPORT_TIME information.