Welcome to WebmasterWorld Guest from 54.145.53.251

Forum Moderators: open

Message Too Old, No Replies

Inner join issue on null return

     
8:29 pm on Mar 19, 2014 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member

joined:Feb 22, 2009
posts:1396
votes: 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
11:01 pm on Mar 19, 2014 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5550
votes: 24


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?
9:38 am on Mar 20, 2014 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member

joined:Feb 22, 2009
posts:1396
votes: 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
9:55 am on Mar 21, 2014 (gmt 0)

New User

joined:Feb 9, 2014
posts: 16
votes: 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.