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 ObjectID
s 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