...

View Full Version : SQL join (?) query help



b_hole
01-25-2007, 09:36 AM
Hello,

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.

mhunt
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

b_hole
01-25-2007, 02:02 PM
I'm getting an empty query. Are you sure about this?

guelphdad
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.

guelphdad
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.

guelphdad
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.

b_hole
01-25-2007, 05:22 PM
Here's the logic in what I'm trying to do:

Employees:
- ID
- NAME
- STATUS (still hired, yes/no)

Reports:
- ID
- EMPLOYEE_ID
- REPORT_TIME
- 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?

guelphdad
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?

b_hole
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.

guelphdad
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?

b_hole
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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum