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 2 of 2
  1. #1
    Regular Coder
    Join Date
    Aug 2006
    Posts
    135
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need help with SELECT statement

    Hi,

    I have 2 simple tables: emps and rprts.
    Table emps has: "ID", "name" (simple string), and some other irrelevant fields.
    Table rprts has: "ID", "emp_id" (indication of "ID" in emps table), "rprt" (10-digits time), and "type" (boolean).

    I want to select the fields "ID" and "name" from emps, and in addition - for every selected row I want to select rprts's "type", where "rprt" (or "id", doesn't matter) value in the highest.


    For example:
    Code:
    emps:
    id        name
    ------------------
    1         jeff
    2         wanda
    3         brad
    
    rprts:
    id        emp_id        rprt        type
    -------------------------------------------
    1            3           df          1
    2            1           ie          1
    3            2           ei          1
    4            3           fd          0
    5            2           tg          0
    6            3           gf          1
    For this data, the following should be selected:

    Code:
    id        name        type
    ----------------------------
    1         jeff         1
    2         wanda        0
    3         brad         1
    I tried several things, but I want to keep it simple and readable, so I'll understand if I'll read it sometime in the future.

    Thanks a lot!

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    we will use the highest id from the reports table. What you need is called a co-related subquery. Lets just concentrate on the rprts table first and show you how that is done and how it works. later we can add the names table as a simple join. Because really you want to know the top id for emp_id 2 and don't care if her name is Mary, Joan or Barbara right?

    Code:
    SELECT
    r.id,        
    r.emp_id,        
    r.rprt,        
    r.type
    FROM rprts AS r
    where r.id =
        (SELECT max(rprts.id) FROM rprts where rprts.emp_id=r.emp_id)
    that joins the rprts table onto itself. for each emp_id it goes through and picks out the highest value for id and returns that row.

    Now we merely join the names table on this by comparing the emps.id with the rprts.emp_id

    Code:
    SELECT
    r.id,
    emps.name        
    r.emp_id,        
    r.rprt,        
    r.type
    FROM emps
    INNER JOIN rprts AS r
    ON emps.id = r.emp_id
    where r.id =
        (SELECT max(rprts.id) FROM rprts where rprts.emp_id=r.emp_id)
    once you see that working correctly you can trim out the unneeded columns, or add others as necessary.


  •  

    Posting Permissions

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