result differences due to joins?

[Ruslan Zasukhin]

On 7/27/06 1:24 PM, "Florian Bogeschdorfer" <fb at memedia.de> wrote:

>> Okay, lets look deeply
>> 
>>> I have some funny results with these sql searches:
>>> 
>>> SELECT * FROM KUNDENTABLE WHERE KD_HASH <>''
>>> =4999
>>> 
>>> SELECT * FROM KUNDENTABLE,KUNDENKARTENTABLE WHERE KD_HASH <>'' AND
>>> CRD_KD_ID=KD_ID
>>> =5129
>>> 
>>> How can this be possible?
>> 
>> 
>> We have inner join..
>> 
>> 4999 - this is number of records?
>> So join give more records than left table ?
>> 
>> Have join by recID + ObjectPtr ?
>> 
>> Have you simply check join table result to see that EXTRA records ?
>> May be some LEFT records are shown few times ?
>> 
> Yes, exactly. I checked this out. What is wrong with my SQL?
> 
> This is part of the structure:
> 
> KUNDENTABLE
> KD_ID indexed, unique
> Name
> KD_HASH unique
> 
> 
> KUNDENKARTENTABLE
> CRD_ID unique
> CRD_KD_ID
> CRD_POINTS
> 
> I link by CRD_KD_ID to KUNDENTABLE. It is one record (KUNDENTABLE) to many
> (KUNDENKARTENTABLE)

Actually what wonders you?
You have 1 : M

So you should see something as:

    1   1
    1   2
    1   3

How many records returns query


SELECT * FROM KUNDENKARTENTABLE WHERE CRD_KD_ID=KD_ID


-- 
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]