CODEWITHSUNDEEP
sqlsqlite
skyork
skyork

Reputation: 7381

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?

Upvotes: 0

Views: 2415

Answers (2)

CL.
CL.

Reputation: 180020

Just use two scalar subqueries (in SQLite, they return NULL when there is no result):

SELECT (SELECT ObjectID
        FROM A
        WHERE ColA = ?
       ) AS ResultA,
       (SELECT ObjectID
        FROM B
        WHERE ColB = ?
       ) AS ResultB

Upvotes: 2

skazska
skazska

Reputation: 421

it is impossible in terms of relational databases and sql sql query is a single operation and can return one result of some data combination (if not to speak about any sql programming like cursor, pl/sql, t-sql etc). As you have to tables and do not want to combine them (and you really don't, you want 2 results of 2 independent manipulations in one query).

possible combinations are:

  • cartesian product of tables - result contains each row of A concatenated with every row of B, so no row of A without row of B
  • join(inner/outer) - result contains concatenations of rows of A and B chosen by some conditions (values of some fields in some subsets of two tables shoul be equal)

so

  1. it is possible to reach conditions:

    • 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".

      select a.ObjectID, b.ObjectId from a [full] outer join b on {some alwais false a.x = b.y} where a.colA = v1 and b.colB = v2

  2. it my be possible to reach conditions:

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

      select a.ObjectID, b.ObjectId from a,b where a.colA = v1 and b.colB = v2

  3. it is impossible to reach:

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

because there are no operation in sql, that return all null dataset.

  1. it is possible to reach conditions:

    • 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".

because cartesian product (cross join) and outer joins are mutually exclusive

Upvotes: -1

Related Questions