May 2, 2006 at 9:02 am
Hi, I cannot get the right result of this query.
select * from customer ---- 461 records
minus
select
* from customerequipment ---- 900 records
the script does not give me the result of the records not in the customerequipment about 439 records that not exist in the customerequipment table.
Jan
May 2, 2006 at 9:32 am
select * from customer
where customer_id not in (select customer_id
from customerequipment)
will give you customers who have no customerequipment records. Reverse the tables in the FROM clauses to get the customerequipment records who have no matching customers.
There has to be a common key (customer_id in my example) to make this work.
I've never seen the MINUS syntax. (I don't get around much anymore) I'd guess that even if it worked, it would only work on sets of rows drawn from the same table, otherwise, how would it know what to subtract?
May 2, 2006 at 10:25 am
It sounds like you might be referring to the EXCEPT clause from SQL 2005.
SELECT * FROM customerequipment -- 900 records
EXCEPT
SELECT * FROM customer --461 records
That will return the rows in customerequipment that are unmatched in customer but only if the table definitions are the same. You might want to use OUTER JOIN syntax to get what you're wanting:
SELECT ce.*
FROM customerequipment ce
LEFT JOIN customer c
ON ce.customer_id = c.customer_id
WHERE c.customer_id IS NULL
Assuming that the common key between the two tables is customer_id of course.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply