results disappearing due to INNER JOIN instead of LEFT JOIN

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

results disappearing due to INNER JOIN instead of LEFT JOIN

Leandro Hermida-2
Hello,

One aspect of BioMart that our users find very confusing is when they
have a result in the web UI and then they want to some column
attributes to display for that result that suddenly it returns no
result rows or far fewer than before. Then they have to figure out
which of the attributes they added is causing this to happen. I think
this is due to the fact that the disappearing rows had no value for
that attribute dimension and it seems BioMart always does INNER JOIN
from the main table across any dimension table instead of a LEFT JOIN
which to me would make more sense? Is there a way to change this
behavior or am I doing something wrong in my mart design?

best,
Leandro
Reply | Threaded
Open this post in threaded view
|

Re: results disappearing due to INNER JOIN instead of LEFT JOIN

Arek Kasprzyk-2
Re: [mart-dev] results disappearing due to INNER JOIN instead of LEFT JOIN
Hi Leandro,
You have diagnosed the problem correctly. BioMart 0.7 SQL compiler performs inner join as a standard. Therefore the data model requires that all the keys from the main tables are also present in the dimensions even if the corresponding rows remain unpopulated. Please make this change to your dm tables and everything should be back to normal. We are planning for 0.8 to add the left join feature as a deployer defined setting


a


On 14/10/10 8:01 AM, "Leandro Hermida" <softdev@...> wrote:

Hello,

One aspect of BioMart that our users find very confusing is when they
have a result in the web UI and then they want to some column
attributes to display for that result that suddenly it returns no
result rows or far fewer than before. Then they have to figure out
which of the attributes they added is causing this to happen. I think
this is due to the fact that the disappearing rows had no value for
that attribute dimension and it seems BioMart always does INNER JOIN
from the main table across any dimension table instead of a LEFT JOIN
which to me would make more sense? Is there a way to change this
behavior or am I doing something wrong in my mart design?

best,
Leandro





Arek Kasprzyk
Director, Bioinformatics Operations and Principal Investigator

Ontario Institute for Cancer Research
MaRS Centre, South Tower
101 College Street, Suite 800
Toronto, Ontario, Canada M5G 0A3
            
Tel:                        416-673-8559
Toll-free:             1-866-678-6427
www.oicr.on.ca
 
This message and any attachments may contain confidential and/or privileged information for the sole use of the intended recipient. Any review or distribution by anyone other than the person for whom it was originally intended is strictly prohibited. If you have received this message in error, please contact the sender and delete all copies. Opinions, conclusions or other information contained in this message may not be that of the organization.