Inner join issue on null return

Forum Moderators: open

Message Too Old, No Replies

Inner join issue on null return

Matthew1980

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

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

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

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.