Query no returning desired resluts, HELP

JimWa · #1 (**permalink**) 10-10-06, 16:20

I have 3tables/views I need to pull into one query for a results set for a Gridview table.

2 views, 1 table.

1st view, vw_ORG has fields, ORGID, ORGNAME

2nd view, vw_Staff, has fields, ORGID, STAFFID, FIRSTNAME, LASTNAME, MIDDLENAME, PREFIX, DEGREE

1st table : tbl_Data, has fields, ORGID, LASTMODIFIEDBY (which is a fk, pk in vw_Staff), LASTMODIFIED (date time ).

Basically my results need to have the ORGID, ORGNAME, LASTMODIFIED DATE and LASTMODIFIEDBY.

Would look like this....

ORGID ORGNAME LASTMODIFIED DATE LASTMODIFIEDBY
1 Science 10/10/2006 9:42 a.m. John P. Smith
22 Mathmatic 10/01/2006 9:15 p.m. Leslie Stahl
95 Football 5/01/2006 12:15 a.m. Terrell Owens

I have gotten results that displays the lastmodified date use MAX but can't figure out how to pull in the names from vw_Staff

Here is that part if you want to use as a starting base.

Select DISTINCT b.orgid, b.OrgName,
(SELECT Max(d.lastmodified)FROM tbl_Data d WHERE d.orgid = b.orgid) AS modifiedDate
From vw_ORGS b
ORDER BY ORGID

I've been banging my head against a wall for 2 days now and am desperatly needing some resolution before the voices in my head start telling me to harm myself j/k.

JimWa · #2 (**permalink**) 10-11-06, 10:21

No one can help with this... ?

r937 · #3 (**permalink**) 10-11-06, 10:43

Code:

select o.ORGID
     , o.ORGNAME
     , d.LASTMODIFIED 
     , s.FIRSTNAME
  from vw_ORG as o
inner
  join tbl_Data as d
    on d.ORGID = o.ORGID       
inner
  join vw_Staff as s
    on s.STAFFID = d.LASTMODIFIEDBY

JimWa · #4 (**permalink**) 10-11-06, 11:15

To many results from your code. but the attmept is most appreciated.

Basically it has to be a MAX date from when the ORG was last modified. Only one row of data per ORG.

r937 · #5 (**permalink**) 10-11-06, 11:18

so the tbl_Data table has multiple "last modified" rows per ORGID?

Code:

select o.ORGID
     , o.ORGNAME
     , d.LASTMODIFIED 
     , s.FIRSTNAME
  from vw_ORG as o
inner
  join tbl_Data as d
    on d.ORGID = o.ORGID     
   and d.LASTMODIFIED =
       ( select max(LASTMODIFIED)
           from tbl_Data
          where ORGID = o.ORGID )   
inner
  join vw_Staff as s
    on s.STAFFID = d.LASTMODIFIEDBY

JimWa · #6 (**permalink**) 10-11-06, 12:14

Yes, that is corrrect.. I was able to get the quasi results needed by using SELECT DISTINCT?

But another issue is that I need to retireve all ORGS weather or not the have a last modified date or not. Is there a IS NULL function or something that can be used to with the select max(LASTMODIFIED) to do this?

Thanks.

r937 · #7 (**permalink**) 10-11-06, 12:15

change INNER to LEFT OUTER in two places

Internet Services

Web Development

Digital Marketing

Consumer Tech