Re: 'not in' and 'not exist'

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Learner (wantnospam_at_email.com)
Date: 03/27/04 

Date: Sat, 27 Mar 2004 15:50:07 +0500

Hi,

Given the scenario that you have mentioned wouldn't the following query
return the same results? Sorry if this sounds silly but your reply will
help me improve my concepts and understanding:

Your suggestion:
> SELECT * FROM complaints c
> WHERE NOT EXISTS
> (SELECT * FROM orders o
> WHERE o.orderDate = c.complaintDate
> AND o.customerID = c.customerID)

Can this also do the same job:
Select * from complaints
Where complaints.orderid = orders.orderid
AND complaints.complaintDate <> orders.orderDate

I'll appreciate your reply and feedback.
Thanks.

> Let's say you have two tables: orders and complaints
>
> each table has a customerID and a date column (which stores just the date at
> midnight).
>
> Now say you wanted to find out the customers who complained, but NOT on the
> same day they placed an order. (And hey, ease up, I'm coming up with this
> fictional crap on the fly. :-))
>
> You can say:
>
> SELECT * FROM complaints c
> WHERE NOT EXISTS
> (SELECT * FROM orders o
> WHERE o.orderDate = c.complaintDate
> AND o.customerID = c.customerID)
>
> You can't say:
>
> SELECT * FROM complaints c
> WHERE customerID and complaintDate
> NOT IN (SELECT customerID, orderDate FROM orders)
>
> You could do it quasi-reliably if you converted both columns, in the
> predicate and the sub-query, and joined them as a single varchar. But you
> would throw performance out the window due to scans.
>
>

Relevant Pages

  • RE: How to left
    ... Do it graphically via the query builder. ... Drag CustomerID, OrderID, & ComplaintID to the query grid. ... FROM (Customer INNER JOIN Complaints ON Customer.CustomerID = ... Complaints.CustomerID) INNER JOIN Orders ON Customer.CustomerID = ...
    (microsoft.public.access.queries)
  • RE: How to left
    ... This works, somewhat, however if there are 4 orders and 0 complaints, the ... Drag CustomerID, OrderID, & ComplaintID to the query grid. ... Complaints.CustomerID) INNER JOIN Orders ON Customer.CustomerID = ...
    (microsoft.public.access.queries)
  • How do I process a query, that pops up automatically, further?
    ... I have a DB of customer complaints. ... query opens a form to inform the user of this (Thanks ... originally used the a totals query for this. ...
    (microsoft.public.access.formscoding)
  • Re: Finding Repeat Complaints
    ... CountAS HowMany ... >From this query, you get a list of all the types of entries that are ... Repeat callers always have 1 of the following things the same - ... > both (we do not always get addresses in the complaints called in to the ...
    (microsoft.public.access.reports)
  • Automatic activation of a macro or event.
    ... I have a complaints data base. ... product type and then does a count if>2 the query will see ... egg as the form opens the query that looks to see if the ... conditions are met but then I want a separate macro to run ...
    (microsoft.public.access.macros)