mardi 28 avril 2015

Select value from two tables if the condition for either table matches

I have two tables A and B that both have a ObjectID column. A has an unique column ColA among other columns, and B has an unique column ColB among others.

I want to do a SELECT based on a pair of given values for ColA and ColB respectively, such that if a value is found in either column, return the value of ObjectID in the corresponding table. If both values are found (i.e. one is found in ColA and the other is found in ColB), return the matching ObjectIDs in both tables (which may or may not be the same). If no value is found for both columns, return empty/null result.

For example, given the tables as below

A
...| ObjectID | ColA |...
        1       123
        3       234
        4       345

B
...| ObjectID | ColB |...
        2      "abc"
        3      "bcd"
        5      "dce"

The query should return (1, 2) if the given value for ColA is 1 and the given value for ColB is "abc".

It should return (3, 3) if the given value for ColA is 234 and the given value for ColB is "bcd".

It should return (4, null) if the given value for ColA is 345 and the given value for ColB is "abcd".

It should return (null, null) if the given value for ColA is 1234 and the given value for ColB is "abcd".

Can this be achieved by a single query? Or do I need to create a view of the involved columns from both tables, and SELECT using the view? If so, is there any performance consideration as this query may be executed quite often?

Aucun commentaire:

Enregistrer un commentaire