...

View Full Version : Need help with SELECT statement



b_hole
03-18-2007, 10:07 AM
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:

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:


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!

guelphdad
03-18-2007, 02:42 PM
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?



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



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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum