CODEWITHSUNDEEP
sqlpostgresqlquery-performance
Phill Pafford
Phill Pafford

Reputation: 85318

Merge two queries into one

I'm not sure if I can do this in one query but I would like to.

I need all records like this:

SELECT a.field_1, a.field_2, b.field_3, b.field_4
  FROM tbl_a AS a, tbl_b AS b
 WHERE a.field_1 = b.field_3

And I would like to also exclude these records that fall into this condition:

IF a.field_1 IN (1,2,3,4) 
AND a.field_date < NOW()

UPDATE: (sorry for the confusion)

Any ideas to get both results into one query

Notes (if this makes a difference):

Upvotes: 1

Views: 142

Answers (3)

bobflux
bobflux

Reputation: 11581

Let's rewrite this with a proper JOIN, and add a NOT on the condition to exclude :

SELECT a.field_1, a.field_2, b.field_3, b.field_4
  FROM tbl_a AS a JOIN tbl_b AS b ON (a.field_1 = b.field_3)
WHERE NOT (a.field_1 IN (1,2,3,4) AND a.field_date < NOW())

Since NOT( X AND Y ) is equivalent to (NOT X) OR (NOT Y) you can rewrite this as :

SELECT a.field_1, a.field_2, b.field_3, b.field_4
  FROM tbl_a AS a JOIN tbl_b AS b ON (a.field_1 = b.field_3)
WHERE a.field_1 NOT IN (1,2,3,4) OR a.field_date >= NOW())

But we'd also need to know if any of field_1 or field_date can be NULL. If that is the case, the expression "foo NOT IN (blah)" returns NULL if foo IS NULL. So you'd need to be more specific about that.

Upvotes: 0

Naim Zard
Naim Zard

Reputation: 405

here's a code that should work:

SELECT a.field_1, a.field_2, b.field_3, b.field_4
FROM tbl_a AS a
INNER JOIN tbl_b AS b ON a.field_1 = b.field_3
WHERE 
(a.field_1 IN (1,2,3,4) AND a.field_date = NOW())
OR
(a.field_1 NOT IN (1,2,3,4))

Note that if a.field is unsigned int != 0 you can replace: a.field_1 IN (1,2,3,4) with a.field_1 <=4 and a.field_1 NOT IN (1,2,3,4) with a.field_1 > 4

this will make it faster in case a large recordset was in question

Upvotes: 0

Hector Sanchez
Hector Sanchez

Reputation: 2317

Something like this?

SELECT a.field_1, a.field_2, b.field_3, b.field_4
FROM tbl_a AS a, tbl_b AS b
WHERE a.field_1 = b.field_3
AND (
CASE WHEN a.field_1 IN (1,2,3,4) THEN
      CASE WHEN A.FIELD_DATE = NOW() THEN 1 ELSE 0
      END
ELSE 1
END) = 1

Upvotes: 2

Related Questions