#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Location
    London
    Posts
    3
    Rep Power
    0

    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.

    (I can't figure out how to make this wrapped like 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
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Location
    Wisconsin
    Posts
    78
    Rep Power
    3
    try something like this

    Code:
    SELECT table1.column1, table1.column2 FROM table1 WHERE table1.column2 NOT IN (SELECT table2.column1 FROM table2)
    use the main query to get your data and the sub query to point to the temp table

    Try to start simple if your still new to this. If a simple query like my example gets you the data you want build off that.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Location
    London
    Posts
    3
    Rep Power
    0
    Thanks, I started off with something like this and still had issues. See the below.

    Code:
    CREATE TABLE #fdiff (foldername VARCHAR(255)) SELECT replace( replace(foldername,'[Template] ','') , '[Archive Template] ','') AS Foldername FROM folderproperties FP with (nolock) WHERE domainid ='39' SELECT FP.FolderName FROM folderproperties FP with (nolock) WHERE FP.[foldername] NOT IN (Select #fdiff.[foldername] FROM #fdiff) DROP TABLE #fdiff


    Originally Posted by deljr
    try something like this

    Code:
    SELECT table1.column1, table1.column2 FROM table1 WHERE table1.column2 NOT IN (SELECT table2.column1 FROM table2)
    use the main query to get your data and the sub query to point to the temp table

    Try to start simple if your still new to this. If a simple query like my example gets you the data you want build off that.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Location
    Wisconsin
    Posts
    78
    Rep Power
    3
    just to confirm you are using mysql correct?

    just looked into your query and (no lock) is a mssql command...
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Location
    London
    Posts
    3
    Rep Power
    0
    Originally Posted by deljr
    just to confirm you are using mysql correct?

    just looked into your query and (no lock) is a mssql command...
    I am using MSSQL apologies, I assumed they were all pretty similar when it comes to basics like this.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,140
    Rep Power
    4274
    Originally Posted by madork
    I am using MSSQL apologies, I assumed they were all pretty similar when it comes to basics like this.
    um... no

    thread moved to sql server forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,140
    Rep Power
    4274
    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

IMN logo majestic logo threadwatch logo seochat tools logo