CODEWITHSUNDEEP
sqlpostgresql
William Wino
William Wino

Reputation: 3819

How do I query this case

DB: PostgreSQL

Suppose I have 

table A(integer a_id, timestamp x, timestamp y) 
table B(int b_id, timestamp n, timestamp m)

m can be null

I want to fetch the data with this structure

SELECT a_id, j.b_id, k.b_id 
FROM A, B AS j, B AS k 
WHERE (x BETWEEN j.n AND j.m) AND (y BETWEEN k.n AND k.m.)

When (x BETWEEN j.n AND j.m) AND (y BETWEEN k.n AND k.m.)'s matches aren't not found

I still want the query to fetch the data with null j.b_id or null k.b_id

How do I do that?

Upvotes: 0

Views: 44

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656321

My interpretation of the cryptic question ...

SELECT a_id, j.b_id, k.b_id 
FROM   a
LEFT   JOIN b j ON a.x BETWEEN j.n AND j.m
LEFT   JOIN b k ON a.y BETWEEN k.n AND k.m

If no matching row is found in j or k, NULL is delivered for j.b_id or k.b_id respectively.

The query suffers from the principal design flaw that multiple matches in b will multiply the number of rows returned in a CROSS JOIN fashion.

Upvotes: 1

Bohemian
Bohemian

Reputation: 424983

Use an outer join and factor in NULLs:

SELECT a_id, j.b_id, k.b_id 
FROM A
LEFT JOIN B AS j ON x >= j.n AND (x <= j.m OR j.m IS NULL)
LEFT JOIN B AS k ON y >= k.n AND (y <= k.m OR k.m IS NULL)

Upvotes: 1

Related Questions