Results 1 to 11 of 11

Thread: weird query

  1. #1
    Join Date
    Nov 2007
    Posts
    7

    Post Unanswered: weird query

    2 tables simplified look like this

    t1
    ----------
    id
    type

    t2
    ----------
    id
    id2
    name
    value
    date

    so say there are rows like this

    t1
    ----------
    1 TESTTYPE

    t2
    ----------
    1 1 NAME TESTNAME 11/7/2007
    2 1 NAME NEWNAME 11/8/2007


    this is to store historical versions of fields for the record in t1 so then is it possible to do a query that will return the newest value for name with the id from t1 so that it comes back

    1 TESTTYPE NEWNAME



    hopefully i have explained this in a not too confusing way thatnks for your help

  2. #2
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Something along the lines:
    Code:
    SELECT t1.id, t1.type, t2.value
    FROM t1, t2
    WHERE t1.id = t2.id2
    AND t2.date = (select max(date) FROM t2 m WHERE m.id2 = t2.id2)

  3. #3
    Join Date
    Nov 2007
    Posts
    7
    not quite what im looking for but i think it is close enough to give me an idea of how to get there (the select max part)

    THANKS!

  4. #4
    Join Date
    Nov 2007
    Posts
    7
    Code:
    SELECT d.doc_id,d.doctype, t2.value as NEW_SARF
    FROM docs d
    	LEFT OUTER JOIN doc_data t2 on d.doc_id = t2.doc_id
    WHERE t2.name = 'NEW_SARF'
    AND t2.doc_update_id = (SELECT MAX(DOC_UPDATE_ID) FROM DOC_DATA WHERE DOC_ID=d.doc_id AND NAME='NEW_SARF')
    this is working but it is not acting as a true outer join and i think its because of the update id check...

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, it's because you're referencing columns from the right table in the WHERE clause

    try like this --
    Code:
    SELECT d.doc_id
         , d.doctype
         , t2.value  as NEW_SARF
      FROM docs d
    LEFT OUTER 
      JOIN doc_data t2 
        ON t2.doc_id = d.doc_id
       AND t2.name = 'NEW_SARF'
       AND t2.doc_update_id = 
           ( SELECT MAX(DOC_UPDATE_ID) 
               FROM DOC_DATA 
              WHERE DOC_ID = d.doc_id 
                AND NAME='NEW_SARF' )
    as you know, in a LEFT OUTER JOIN when a row from the left table has no match in the right table, all the columns from the right table in the result row are NULL

    thus, they cannot be equal to anything, so if you use a column from the right table in the WHERE clause, you are essentially filtering out all unmatched rows
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2007
    Posts
    7
    i see what your saying but putting that subquery in the join gives you a

    "ORA-01799: a column may not be outer-joined to a subquery when migrating"

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, then, don't do it while migrating

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Nov 2007
    Posts
    7
    is it anything like this http://www.dbforums.com/showthread.php?t=895027

    where you have perhaps tackled this problem before?

  9. #9
    Join Date
    Nov 2007
    Posts
    7
    i think i figured it out using inline views

    Code:
    select d.*, d1.SITENAME, d2.NEW_SARF FROM docs d, (SELECT t2.doc_id, t2.value as SITENAME
    FROM doc_data t2
    WHERE t2.name = 'SITENAME'
    AND t2.doc_update_id = (
    	SELECT MAX(DOC_UPDATE_ID)
    	FROM DOC_DATA
    	WHERE DOC_ID=t2.doc_id
    	AND NAME='SITENAME'
    )) d1, (SELECT t2.doc_id, t2.value as NEW_SARF
    FROM doc_data t2
    WHERE t2.name = 'NEW_SARF'
    AND t2.doc_update_id = (
    	SELECT MAX(DOC_UPDATE_ID)
    	FROM DOC_DATA
    	WHERE DOC_ID=t2.doc_id
    	AND NAME='NEW_SARF'
    )) d2
    WHERE d.doc_id = d1.doc_id (+)
    AND d.doc_id = d2.doc_id (+) ORDER BY d.DOC_ID

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i should like to point out that your inline views are subqueries, and you are joining to them using an outer join

    but nice work figuring it out

    please try this, it should be the same but without those horrid plus signs ...
    Code:
    select d.*
         , d1.SITENAME
         , d2.NEW_SARF 
      FROM docs d
    LEFT OUTER
      JOIN ( SELECT t2.doc_id
                  , t2.value as SITENAME
               FROM doc_data t2
              WHERE t2.name = 'SITENAME'
                AND t2.doc_update_id = 
                    ( SELECT MAX(DOC_UPDATE_ID)
                       FROM DOC_DATA
                      WHERE DOC_ID = t2.doc_id
                        AND NAME='SITENAME' )
           ) d1
        ON d1.doc_id = d.doc_id 
    LEFT OUTER
      JOIN ( SELECT t2.doc_id
                  , t2.value as NEW_SARF
               FROM doc_data t2
              WHERE t2.name = 'NEW_SARF'
                AND t2.doc_update_id = 
                    ( SELECT MAX(DOC_UPDATE_ID)
                        FROM DOC_DATA
                       WHERE DOC_ID = t2.doc_id
                         AND NAME='NEW_SARF' )
           ) d2
        ON d2.doc_id = d.doc_id 
    ORDER 
        BY d.DOC_ID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Nov 2007
    Posts
    7
    yeah i knew it was ugly but i had just gotten it working and was happy i have no oracle experience and have just been piecing this stuff together from web articles

Posting Permissions

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