Return values not in joined table

Dev Shed Forums Sponsor:

July 24th, 2012, 06:01 AM

madork

Registered User

Join Date: Jul 2012

Location: London

Posts: 3

Time spent in forums: 46 m 31 sec
Reputation 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

July 24th, 2012, 06:10 AM

deljr

Contributing User

Join Date: Jun 2012

Location: Wisconsin

Posts: 78

Time spent in forums: 1 Day 11 h 51 m 6 sec
Reputation Power: 2

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.

July 24th, 2012, 06:43 AM

madork

Registered User

Join Date: Jul 2012

Location: London

Posts: 3

Time spent in forums: 46 m 31 sec
Reputation 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

Quote:

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)

July 24th, 2012, 06:57 AM

deljr

Contributing User

Join Date: Jun 2012

Location: Wisconsin

Posts: 78

Time spent in forums: 1 Day 11 h 51 m 6 sec
Reputation Power: 2

just to confirm you are using mysql correct?

just looked into your query and (no lock) is a mssql command...

July 24th, 2012, 07:00 AM

madork

Registered User

Join Date: Jul 2012

Location: London

Posts: 3

Time spent in forums: 46 m 31 sec
Reputation Power: 0

Quote:

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.

July 24th, 2012, 07:23 AM

r937

SQL Consultant

Dev Shed God 43rd Plane (26000 - 26499 posts)

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,442

Time spent in forums: 3 Months 1 Week 2 Days 14 h 16 m 32 sec
Reputation Power: 4141

Quote:

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

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

July 24th, 2012, 07:30 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,442

Time spent in forums: 3 Months 1 Week 2 Days 14 h 16 m 32 sec
Reputation Power: 4141

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

Return values not in joined table

Developer Shed Advertisers and Affiliates