FW: AW: Joins in 3.5

[Ruslan Zasukhin]

------ Forwarded Message
From: Fabian Eschrich <info at faesch.de>
Date: Mon, 10 Dec 2007 19:35:13 +0100
To: 'Ruslan Zasukhin' <sunshine at public.kherson.ua>
Subject: AW: Joins in 3.5

Hi Ruslan,

thanks for your reply.

Here are 2 queries:

Select
 addresses.id,addresses.categoryid,
addresses.type,addresses.customernumber,addresses.name1,addresses.name2,addr
esses.street,addresses.zip,addresses.city,addresses.country

from addresses
left outer join address_categories on addresses.categoryid =
address_categories.id

where  addresses.categoryid = '200712101451563674321'


----- all right - returns 92 rows. Now the other left outer join.

select addresses.id,addresses.categoryid,
addresses.type,addresses.customernumber,addresses.name1,addresses.name2,addr
esses.street,addresses.zip,addresses.city,addresses.country,
 CONCAT(commfon.value1,'-',commfon.value2,'-',commfon.value3) as fon

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

where  addresses.categoryid = '200712101451563674321'

------ returns only 55 rows. These are exactly the 55 rows that have a
corresponding entry in table communication. But I think a left outer join
should return all rows with NULL values for those, that have no
corresponding entry in table communication.


Mit freundlichen Grüßen / kindly regards,

Fabian Eschrich
FAESCH Software

info at faesch.de
 
Fon:     +49 (0)37206 889293
Fax:     +49 (0)37206 889294
Skype:   feschrich
SIPgate: 9000760

-----Ursprüngliche Nachricht-----
Von: Ruslan Zasukhin [mailto:sunshine at public.kherson.ua]
Gesendet: Montag, 10. Dezember 2007 18:41
An: Fabian Eschrich; valentina at lists.macserve.net
Betreff: Re: Joins in 3.5

On 10/12/07 4:04 PM, "Fabian Eschrich" <info at faesch.de> wrote:

Hi Fabian,

> Hi Ruslan,
> 
> didn't you change the way valentina works with joins in release 3.5?

We did !

And this was one of major 3.5 addition,

Actually we did not change, but improve it.
We have add ability specify FILTER conditions in the ON clause

SELECT 
FROM T1 join T2 ON T1.id = T2.ptr AND T1.f1 = 15 AND T2.f3 < 22
                                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
 
This is SQL Standard Syntax.

And it ESPECAILLY important of used with OUTER JOINS.

> I still have the problem, if table b has no records corresponding to table
> a, the left outer join query does not return the whole row.

I not remember if this was you who have request this feature...

Okay, show us your task.
    db, query...


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



------ End of Forwarded Message