CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
swolff1978
swolff1978

Reputation: 1865

How do I 'subtract' sql tables?

Its not really a subtraction I'm looking for. And I know its not a union or intersection... I have been given a long and complex stored procedure that returns a table of active and inactive documents. I have also been given a similar stored procedure that returns another table that contains only the active documents.

How could I get a table of inactive documents using these two store procedures?

We are using SQL Server 2005.

Upvotes: 38

Views: 74429

Answers (14)

jerryurenaa
jerryurenaa

Reputation: 4712

if you are substracting a percentage and updating a table use this

UPDATE `TABLENAME` SET `COLUMNNAME` = (50 / 100) * `COLUMNNAME`;

please note that the (50 / 100) is for 50% so in this case this query will discount 50% from the total value of the row!

Upvotes: 0

Aziz Altamimi
Aziz Altamimi

Reputation: 17

You can just used the first sp that return the Active & Inactive and in WHERE cluse put condition for the document status =inactive, you wil get inactive document only.

Upvotes: 0

Ed B
Ed B

Reputation: 796

You can also do this with the NOT IN clause

For example, assuming the stored procedures have given you table variables called @AllDocuments and @ActiveDocuments and each document has an identifier column called DocId

SELECT * FROM @AllDocuments 
WHERE DocId NOT IN 
    (SELECT DocId FROM @ActiveDocuments)

Adapt this as appropriate to match your table / column names.

Upvotes: 1

suvidha rane
suvidha rane

Reputation: 1

For doing the subtraction between three tables I have used the following query:

Basically I have three tables.. table 1, table 2, table 3. Firstly I have done the subtraction of table 1 and table 2 and then done the subtraction between the the result of previous query and table 3. 

select v3.Material, ((v1.Qty-v2.Qty)-v3.Qty) as Quantity
  from table1 v1, table2 v2, table3 v3
 where (v1.Material=v2.Material
    and v1.Material=v3.Material
    and v2.Material=v3.Material)

Upvotes: 0

Md.Alauddin Hossain
Md.Alauddin Hossain

Reputation: 1

SELECT roll_number FROM profile WHERE(catagory='Attest and Eat' or catagory='Live and Eat') and status='OK' EXCEPT SELECT roll_number from meal_status  WHERE date='29' AND month='1'

You can try this kind of command to subtract a table from another one.

Upvotes: 0

gbn
gbn

Reputation: 432471

All good answers, but missing one point: The questioner (OP) has stored procedures...

You have to define temporary tables (based on your platform) to load the data

INSERT ...
EXEC getActive

INSERT ...
EXEC getInactive

Then use EXCEPT/EXISTS/MINUS/IN/OUTER JOIN/etc...

Upvotes: 9

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181370

What's your DB engine?

In Oracle, you could use MINUS set operation.

In MS SQLServer 2005 and newer you can use EXCEPT.

Upvotes: 3

LBushkin
LBushkin

Reputation: 131776

The set operation you are looking for is called MINUS, but in SQL Server the keyword is EXCEPT

  SELECT ... // all documents
  EXCEPT
  SELECT ... // active documents

I believe that the EXCEPT set operation became available in SQL Server 2005.

Upvotes: 57

Michael Krelin - hacker
Michael Krelin - hacker

Reputation: 143229

SELECT both.*
FROM both LEFT OTUER JOIN inactives USING (whatever_id)
WHERE inactives.whatever_id IS NULL;

or

SELECT * FROM both
EXCEPT
SELECT * FROM inactives;

Upvotes: 2

Jason S
Jason S

Reputation: 189816

SELECT * FROM Table1 
LEFT JOIN Table2 on Table1.id = Table2.id
WHERE Table2.id IS NULL

this should work on almost any database engine

Upvotes: 15

Matthew Jones
Matthew Jones

Reputation: 26190

In MS TSql, I think you want the EXCEPT keyword.

query1 EXCEPT query2

Which will return all rows found in the first query that are not also found in the second query.

Upvotes: 1

Dave Pirotte
Dave Pirotte

Reputation: 3816

I believe EXCEPT is what you are looking for. Syntax is similar to UNION or INTERSECT.

Upvotes: 3

Carl Manaster
Carl Manaster

Reputation: 40356

Assuming there are unique IDs that correspond across the two tables:

select * from table_both b
where not exists (select * from table_active a where a.id = b.id)

Upvotes: 13

tsilb
tsilb

Reputation: 8037

select * from MyTable1
where MyTable1.Field1 not in (
  select Field1 from MyTable2)

Upvotes: 4

Related Questions