homepage Welcome to WebmasterWorld Guest from 54.204.249.184
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Inner join issue on null return
Matthew1980




msg:4655524
 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




msg:4655582
 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




msg:4655686
 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:4655942
 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