Hi,

I have a query:

select assettag from amPortfolio EXCEPT(select assettag from amComputer)

provided that these two table has a commom field assettag and they do not
have identical fields.

I would like to transform the same query using a WHERE clause.

Would anybody have idea how to do it? Can use anything like INTERSECTS, UNION...

Thanks,

Mike

The simplest way is
select assettag from amPortfolio where assettag not in (select assettag from amComputer)

I tried that.

Strange that it returns 0 records...

I used:

select assettag from amPortfolio where assettag in (select assettag from amComputer)

It gives me 5482 records..

I then did

select count(distinct assettag) from amComputer

It gives me 5510

Then

select count(distinct assettag) from amPortfolio

It gives me 8010

I know obviously some assettag in amPortfolio is not in amComputer... but somehow I got 0 records when doing the query..

If you add a condition in the field of the outer table of a except join, you will receive 0 row because execpt returns the fields that have no concordance.

for this type of query, prefer the "where not exists (select... from ... where...)"