Changing Join Query

[Ivan Smahin]

Hello Fabian,

Monday, October 22, 2007, 11:00:39 AM, you wrote:

> Hi,

> as I wrote in another message before, I am converting my app from mysql to
> valentina.

> Until today I used a join query to load records from my address table and
> multiple records for each from a communication table (which holds
> fonnumbers, fax, mail and so on).

> select addresses.id,addresses.name1,addresses.name2,

> CONCAT(commfon.value1,'-',commfon.value2,'-',commfon.value3) as fon,
> CONCAT(commfax.value1,'-',commfax.value2,'-',commfax.value3) as fax,
> commmail.value1 as mail from addresses

> LEFT OUTER JOIN communication AS commfon ON commfon.type = 'fon' AND
> commfon.record = addresses.id
> LEFT OUTER JOIN communication AS commfax ON commfax.type = 'fax' AND
> commfax.record = addresses.id
> LEFT OUTER JOIN communication AS commmail ON commmail.type = 'mail' AND
> commmail.record = addresses.id


It is hard to even read :).
So original query is:
select
        addresses.id,
        addresses.name1,
        addresses.name2,
        CONCAT(commfon.value1,'-',commfon.value2,'-',commfon.value3) as fon,
        CONCAT(commfax.value1,'-',commfax.value2,'-',commfax.value3) as fax,
        commmail.value1 as mail 
from 
        addresses LEFT OUTER JOIN communication AS commfon ON commfon.type = 'fon' AND commfon.record = addresses.id
        LEFT OUTER JOIN communication AS commfax ON commfax.type = 'fax' AND commfax.record = addresses.id
        LEFT OUTER JOIN communication AS commmail ON commmail.type = 'mail' AND commmail.record = addresses.id


I think this one will work:
select
        addresses.id,
        addresses.name1,
        addresses.name2,
        CONCAT(commfon.value1,'-',commfon.value2,'-',commfon.value3) as fon,
        CONCAT(commfax.value1,'-',commfax.value2,'-',commfax.value3) as fax,
        commmail.value1 as mail 
from 
        addresses LEFT OUTER JOIN communication AS commfon ON commfon.record = addresses.id
        LEFT OUTER JOIN communication AS commfax ON commfax.record = addresses.id
        LEFT OUTER JOIN communication AS commmail ON commmail.record = addresses.id
WHERE
        commfon.type = 'fon'
        AND commfax.type = 'fax'
        AND commmail.type = 'mail'


If no - send me the database please for playing.


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