Have a flat result?

[inarobis]

Hello guys,

I would like to have a flat result of this query result.

This is my result doing my normal query with left join and inner join throught tables (PHONE, PERSON, EMAIL, ADDRESS )


Phone eMail City Fax
Mariam 2135262 NULL NULL NULL
Mariam NULL mari@mari.na NULL NULL
Mariam NULL NULL Napoli NULL
Mariam NULL NULL NULL NULL


How can I have this result?

Phone eMail City Fax
Mariam 2135262 mari@mari.na Napoli NULL


thanks on advance

Ina

 

[AngelB]

Hi there,

What you are looking for is the MAX function. Try this:

SELECT Name_Field,
       MAX(Phone),
       MAX(eMail),
       MAX(City),
       MAX(Fax)
FROM (Tables and Joins here)
GROUP BY Name_Field

You may run into problems with this approach if there is more than one person with the same name on your tables. In this case you may get Phone, email, city or fax details from the wrong person if their 'value' is larger than that of the person you actually want to return


Geraint

The lights are on but nobody's home, my elevator doesn't go to the top. I'm not playing with a full deck, I've lost my marbles. Barenaked Ladies - Crazy