Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Posts
    40

    Unanswered: Query no returning desired resluts, HELP

    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.

  2. #2
    Join Date
    Jan 2004
    Posts
    40

    Unhappy

    No one can help with this... ?

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2004
    Posts
    40
    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.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2004
    Posts
    40
    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.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    change INNER to LEFT OUTER in two places
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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