homepage Welcome to WebmasterWorld Guest from 54.197.183.230
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Inner join issue on null return
Matthew1980

WebmasterWorld Senior Member 5+ Year Member



 
Msg#: 4655522 posted 8:29 pm on Mar 19, 2014 (gmt 0)

Hi there Gurus,

Sorry to be a pain! I've been asked to optimise an existing application that queries 3 or four tables, and now I'm adding another table.

Ideally if data on one inner join returns null, I want to try another table, but I can't do two queries because of the way this whole thing works.

My question is:-

How can I stop the WHOLE query from running/returning no records if one of the inner joins returns nothing?

I've been puzzling over this for a few days now, and I'm sure as it's achievable, just need a pointer as to how to put an if else type thing in there!

Thanks for reading,

Cheers,
MRb

 

LifeinAsia

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4655522 posted 11:01 pm on Mar 19, 2014 (gmt 0)

Not exactly sure what you're asking... If any inner join returns no values, there's nothing to join against for the remaining joins, and the whole query will return nothing..

As far as the "if one join returns null then try another table" issue, I have some ideas, but I wouldn't call them optimized as you'd be doing both joins each time, even if the second wasn't needed.

Maybe you can give a little more detail?

Matthew1980

WebmasterWorld Senior Member 5+ Year Member



 
Msg#: 4655522 posted 9:38 am on Mar 20, 2014 (gmt 0)

Hi there LifeInAsia,

Yes, sorry for the vagueness but I didn't have time to write pseudo code, and I don't want to have my information public!

I have a feeling I will HAVE to do 2 queries, but if I can do an if else type arrangement then good-o!

When I'm at my PC, I will post something more relevant.

Thanks,
MRb

DaveWave



 
Msg#: 4655522 posted 9:55 am on Mar 21, 2014 (gmt 0)

Use COALESCE(field1, field2, field3, field4) which will return the first non-null value. Thats providing you can do a query with all four fields.

It might be more practical to create a procedure to handle your logic.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved