[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Two Table Compare

Posted on 2012-08-24
Medium Priority
Last Modified: 2012-08-24
I am needing to compare two tables in SAS (using SQL sytax) to find out what records in Table A is in Table B, and if they are not in Table B, then I want to add a column to the query to mark them as 'New'.

      create table TableC as
      select a.*, 'New' as Type
      from TableA a
      where a.id not in (select id from TableB);

Obviously my query is currently only getting those records that don't match and creating the new column...but there has to be a way to do this all in one query, or maybe I'm wrong.
Question by:Lee R Liddick Jr
1 Comment
LVL 75

Accepted Solution

Anthony Perkins earned 2000 total points
ID: 38329487
Something like this perhaps:
SELECT  a.*,
        CASE WHEN b.id IS NULL THEN 'New'
             ELSE NULL
        END AS Type
FROM    TableA a
        LEFT JOIN TableB b ON a.id = b.id

Open in new window


Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

What we learned in Webroot's webinar on multi-vector protection.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

872 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