Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 11 of 11
  1. #1
    Regular Coder
    Join Date
    Aug 2006
    Posts
    135
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Post SQL join (?) query help

    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. Can someone please explain what I'm missing?

    Thanks a lot.

  • #2
    Regular Coder
    Join Date
    Mar 2005
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts
    PHP Code:
    SELECT tbl1.idtbl2.mytime FROM tbl1 LEFT JOIN tbl2 ON tbl1.id tbl2.tbl1id WHERE tbl1.status AND tbl2.type 1 ORDER BY tbl1.id ASC 

  • #3
    Regular Coder
    Join Date
    Aug 2006
    Posts
    135
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm getting an empty query. Are you sure about this?

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    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.

  • #5
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    Quote Originally Posted by b_hole View Post
    I never fully understood JOIN
    Here is an article that might help you understand joins. It is pretty basic.

  • #6
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    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.

  • #7
    Regular Coder
    Join Date
    Aug 2006
    Posts
    135
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?

  • #8
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    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?

  • #9
    Regular Coder
    Join Date
    Aug 2006
    Posts
    135
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorry, I should have clarify this: REPORT_TIME (mytime in the picture) is in 10-digits format, that means year+month+day+time.

  • #10
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    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?

  • #11
    Regular Coder
    Join Date
    Aug 2006
    Posts
    135
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm using PHP's time() function. What's the difference?

    Quote Originally Posted by guelphdad View Post
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •