Solved

Select where table1 not in table2

Posted on 2006-07-20
3
757 Views
Last Modified: 2012-05-05
I have 2 tables.

Table1 is made of 2 primary keys, table_id and job_id along with a bunch of other values (lets just use job_name for now).
Table2 is made of 2 primary keys, table_id and job_id along with a bunch of other values (Don't care what for this example).

I need to select the job_name from table1 where that entries table_id and job_id are not in table2.

How can I do this?  I was using an outter join when I wanted table 2 with table 1 but now that I only want table1 when there is no matchig entry in table 2!!

Thanks!
0
Comment
Question by:JRamos1200
  • 2
3 Comments
 
LVL 25

Accepted Solution

by:
Mr_Peerapol earned 125 total points
ID: 17147254
SELECT t1.*
FROM Table1 t1
WHERE NOT EXISTS (SELECT 1 FROM Table2 t2 WHERE t1.table_id = t2.table_id AND t1.job_id = t2._jobid)
0
 

Author Comment

by:JRamos1200
ID: 17147495
That did it!  Thanks.  What does select 1 mean anyway?
0
 
LVL 25

Expert Comment

by:Mr_Peerapol
ID: 17147523
Just the constant number 1. In the EXISTS clause the list of fields returned by inner SELECT doesn't matter because EXISTS() just checks whether there is a record returned by the SELECT statement no matter what columns included.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question