CODEWITHSUNDEEP
sql
Marco Hernández
Marco Hernández

Reputation: 305

conditionally select from different tables

I have two tables storing materials:

Table ProductsA

 ID | Description   | Date
 -----------------------------
 1  | Apple Iphone  | 01.03.2019

 Table ProductsB

 ID | Description     | Date
 -------------------------------
 1  | Apple Iphone    | 20.03.2019
 2  | Samsung Galaxy  | 20.03.2019
 3  | Dell Laptop     | 20.03.2019

I want to return results from ProductsB if a first query to ProductsA does not return anything. For instance, I want to return the products with date greater than 05.03.2019.

I tried:

SELECT COALESCE(A.ID, B.ID) as ID,
       COALESCE(A.Description, B.Description) as Description
FROM ProductsA  as A
INNER JOIN ProductsB as B on B.ID = A.ID
WHERE A.Date >= 05.03.2019

But it's not really working. If A return null values, then there's nothing to join. How could I achieve it?

EDIT: I would expect to return values from ProductsA, and from ProductsB if and only if nothing was found from ProductsA. Following the same reasoning, I would expect this result (querying for products with date later than 05.03.2019):

ID | Description     | Date
-------------------------------
1  | Apple Iphone    | 20.03.2019
2  | Samsung Galaxy  | 20.03.2019
3  | Dell Laptop     | 20.03.2019

Edit2: However, for results with date Greater than equal 01.03.2019, I should return only:

ID | Description   | Date
 -----------------------------
1  | Apple Iphone  | 01.03.2019

Upvotes: 1

Views: 49

Answers (1)

Morten
Morten

Reputation: 414

You can use UNION like this:

SELECT A.ID,
       A.Description
FROM ProductsA AS A
WHERE A.Date >= '2019.03.05'

UNION ALL

SELECT B.ID,
       B.Description
FROM ProductsB AS B
WHERE B.Date >= '2019.03.05'
AND NOT EXISTS ( SELECT 1 FROM ProductsA AS A2 WHERE A2.Date >= '2019.03.05' );

Upvotes: 2

Related Questions