On 11/30/05 5:23 PM, "Ivan Smahin" <[EMAIL PROTECTED]> wrote:
> Hello Florian, > > Tuesday, November 29, 2005, 9:33:22 PM, you wrote: > >> Hi all! >> >> Say I have two tables: >> >> TABLE CUSTOMER with fields ID,NAME,ADRESS >> >> And >> >> TABLE PAYBACKCARD with fields CUSTOMER_ID,ID,CARDNUMBER >> >> If I search for customers with cards, I do "SELECT * FROM >> CUSTOMER,PAYBACKCARD WHERE CUSTOMER.ID=PAYBACKCARD.CUSTOMER_ID" >> >> Fine. But now I want to find all customers WITHOUT card. How can I do >> this? >> In this case there is no link between the two tables, I need >> "SELECT * FROM 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. Florian, It is not clear from your letter if you have FOREIGN KEY for this table. IF you have it, then you have LINK from Valentina point of view. And then wow, you can use MUCH MORE effective SQL than above 2 standard ways: > 1. SELECT * FROM t1 WHERE COUNT_LINKED(t1, LNK_t1_t2) = 0 Right. You can see that in this case Valentina do not need do JOIN and other hard operations. Valentina will just use link. This is much faster. > 2. SELECT * FROM t1 WHERE t1->CUSTOMER_ID is NULL Ivan, this query will require jumps to second table, so it is not so effective. -- Best regards, Ruslan Zasukhin VP Engineering and New Technology Paradigma Software, Inc Valentina - Joining Worlds of Information http://www.paradigmasoft.com [I feel the need: the need for speed] _______________________________________________ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
