Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2012
    Location
    London
    Posts
    2

    Question Unanswered: Return values not in joined table

    Hello,
    I'm relatively new so please ignore my ignorance and amateur skills.

    Basically, I'm looking to pull back some results but not include those that have a value in a column that matches a value in a temp table column.

    The below code returns all the values rather than excluding those that are in the temp table.

    Any ideas?

    Thanks!!

    Code:
    ------Folder Differences-----
    --Create Temp Table--
    CREATE TABLE #fdiff 
    
    (foldername VARCHAR(255))
    
    SELECT
    replace(
    replace(foldername,'[Template] ','')
    , '[Archive Template] ','') AS Foldername
    
    FROM 
    folderproperties FP with (nolock)
    WHERE domainid ='39'
    
    --Folder Differences--
    SELECT dp.Domainname, dp.Domainid, fp.Foldername, fd.[foldername]
    
    FROM DomainProperties DP with (nolock) Inner join
    folderproperties FP with (nolock) on dp.Domainid = fp.domainid left outer join
    #fdiff fd with (nolock) on fp.[foldername] = fd.[foldername]
    
    WHERE
    NOT EXISTS (select foldername From #fdiff )
    AND
    dp.Domainid NOT IN ('39','1','2','58')
    
    DROP TABLE #fdiff

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT dp.Domainname
         , dp.Domainid
         , fp.Foldername
         , fd.[foldername]
      FROM DomainProperties DP with (nolock) 
    Inner 
      join folderproperties FP with (nolock) 
        on fp.domainid = dp.Domainid
    left outer 
      join #fdiff fd with (nolock) 
        on fd.[foldername] = fp.[foldername]
     WHERE dp.Domainid NOT IN ('39','1','2','58')
       AND fd.[foldername] IS NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2012
    Location
    London
    Posts
    2
    Thanks,
    I've realised (after several hours of me looking blankly at my screen) that I forgot to put 'insert into' into my temp table.

    Really love it when I miss the simplest things!!

Posting Permissions

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