AW: AW: Joins in 3.5

[Ivan Smahin]

Hello Fabian,

Tuesday, December 11, 2007, 9:43:33 AM, you wrote:

FE> Hi,

FE> thanks for your reply.
FE> I think I understand what you are saying.

FE> So shouldn't a query like this work?

FE> select addresses.id,addresses.categoryid,

FE>     CONCAT(commfon.value1,'-',commfon.value2,'-',commfon.value3) as fon

FE> from addresses
FE> left outer join address_categories ON addresses.categoryid =
FE> address_categories.id
FE> LEFT OUTER JOIN communication AS commfon ON commfon.addressid = addresses.id
FE> AND commfon.type = 'fon'

FE> where  addresses.categoryid = '200712101451563674321'


FE> --------------
FE> For table addresses and address_categories there are always matching
FE> records. So this are 92 records for the categoryid specified.

FE> Now joining the table communication which holds fonnumbers, mailadresses and
FE> so on. I am joining to the addressed and the type fon, because I want fon
FE> numbers. The 55 results now are only these, that have a fon numbers. NULL
FE> records are not present, so I think the join condition is filtered at the
FE> end of the query currently. I send you my current demo database attached.

I  have  spent  an  hour  thinking of this until I get the answer just
playing with your database. The answer is very simple.
Look,

For example:
select addressid, type
from communication
where recID in (55,56,57)

returns 3 records with the same addressid and different types.

Let's consider only these records:
1. addresses (33)
2. communication(55,56,57).

When you do 'addresses LEFT OUTER JOIN communication ON join_condition' you get at least
3   records   in   the  result.  Now you want to consider records with
type <> 'fon' as unlinked to any record from addresses table.

select
        addresses.id,
        commfon.type,
        addresses.recID,
        commfon.recID
    
from 
        addresses LEFT OUTER JOIN communication AS commfon ON commfon.addressid = addresses.id
              AND commfon.type = 'fon'
where  
        addresses.categoryid = '200712101451563674321'
        and commfon.recID in (55,56,57)

So you get single row if type 'fon' is found among that 3 rows (55,56,57).
Records  56, 57 now considered as unlinked to any records! We have
single linked pair - (33,55).


Well, I will check it again - today evening.


-- 
Best regards,
Ivan Smahin 
Senior Software Engineer
Paradigma Software, Inc
Valentina - The Ultra-Fast Database
http://www.valentina-db.com