SQL question

[Ivan Smahin]

Hello Florian,

Tuesday, November 29, 2005, 9:33:22 PM, you wrote:

FB> Hi all!

FB> Say I have two tables:

FB> TABLE CUSTOMER with fields ID,NAME,ADRESS

FB> And

FB> TABLE PAYBACKCARD with fields CUSTOMER_ID,ID,CARDNUMBER

FB> If I search for customers with cards, I do "SELECT * FROM
FB> CUSTOMER,PAYBACKCARD WHERE CUSTOMER.ID=PAYBACKCARD.CUSTOMER_ID"

FB> Fine. But now I want to find all customers WITHOUT card. How can I do this?
FB> In this case there is no link between the two tables, I need "SELECT * FROM
FB> CUSTOMER WHERE there is no link between the tables..."


There is at least 2 ways.

1. SELECT * FROM t1 WHERE t1.id NOT IN ( SELECT RecID FROM T1,T2 ...)

2. SELECT * FROM t1
   EXCEPT
   SELECT RecID FROM T1,T2 ...

Also. You can consider to have a link between tables.
So you will get a lot of link-specific opportunities.


1. SELECT * FROM t1 WHERE COUNT_LINKED(t1, LNK_t1_t2) = 0

2. SELECT * FROM t1 WHERE t1->CUSTOMER_ID is NULL

...


-- 
Best regards,
 Ivan                            mailto:IvanSmahin at public.kherson.ua