CODEWITHSUNDEEP
sqlite
user985351
user985351

Reputation: 197

Sqlite select from tableA if not match in tableB

cmd.CommandText = @"SELECT * FROM tableA, tableB 
  WHERE tableA.someid = tableB.someid AND 
  UPPER(name) LIKE @name LIMIT 1";

cmd.Parameters.AddWithValue("@name", _name.ToUpper() + "%");

Is it possible to select from tableA where the name matches, even if tableA.someid = tableB.someid condition fails? Currently this query returns no data, if tableB does not have matching someid, but has matching name.

if tableA.someid = tableB.someid && UPPER(name) LIKE @name
    return all data from both tables

if tableA.someid != tableB.someid
    return all from tableA where UPPER(name) LIKE @name

Upvotes: 0

Views: 83

Answers (2)

CL.
CL.

Reputation: 180300

If you want to join two tables and to get records that do not match, you need an outer join:

SELECT *
FROM tableA
LEFT JOIN tableB ON tableA.someid = tableB.someid
WHERE UPPER(name) LIKE @name
LIMIT 1

Upvotes: 2

André Stannek
André Stannek

Reputation: 7863

You could use EXISTS to check for an entry and UNION SELECT to join two results:

SELECT * FROM tableA, tableB WHERE tableA.someid = tableB.someid UNION SELECT * FROM tableA WHERE NOT EXISTS (SELECT * FROM tableA, tableB WHERE tableA.someid = tableB.someid)

This way you get the entries with values from both tables where the id's match and the values from table A where there is no mathing id in table B.

A problem with that is, that both selects of the union must have the same number of result columns. You could work around it by specifing the columns manually and filling up with null:

SELECT colA1, colA2, colB1, colB2 FROM tableA, tableB WHERE tableA.someid = tableB.someid UNION SELECT colA1, colA2, null, null FROM tableA WHERE NOT EXISTS (SELECT * FROM tableA, tableB WHERE tableA.someid = tableB.someid)

Upvotes: 0

Related Questions