Checking for multiple values in multiple columns, sql select

Question

I have a pretty SQL select query that works as follows:

select * from session
where date(ts) = '2017-10-16'
and 7200 in (callingpartyno,finallycalledpartyno);

This retrieves 24 records as it should. The problem now is that I have 14 other extensions to check for within those same 2 columns and I don't know how to look for multiple values within multiple columns.

Let's say the previous query returns 24 and I do the same query with 7201 and it returns 6 rows. I want a way that would return 30 rows in that case. Same goes for 7200 through 7213, so to pull any row with that time stamp that has ANY of those 13 extensions in EITHER of those columns.

Is there a way to do this?

I tried like this:

select * from ambition.session 
where date(ts) = '2017-10-16'
and 7276 in (CALLINGPARTYNO, FINALLYCALLEDPARTYNO)
and 7314 in (CALLINGPARTYNO, FINALLYCALLEDPARTYNO)
and 7295 in (CALLINGPARTYNO, FINALLYCALLEDPARTYNO)
and 7306 in (CALLINGPARTYNO, FINALLYCALLEDPARTYNO)
and 7357 in (CALLINGPARTYNO, FINALLYCALLEDPARTYNO)
and 7200 in (CALLINGPARTYNO, FINALLYCALLEDPARTYNO) 
and 7218 in (CALLINGPARTYNO, FINALLYCALLEDPARTYNO)
and 7247 in (CALLINGPARTYNO, FINALLYCALLEDPARTYNO)
and 7331 in (CALLINGPARTYNO, FINALLYCALLEDPARTYNO) 
and 7255 in (CALLINGPARTYNO, FINALLYCALLEDPARTYNO)
and 7330 in (CALLINGPARTYNO, FINALLYCALLEDPARTYNO)
and 7000 in (CALLINGPARTYNO, FINALLYCALLEDPARTYNO)
and 7215 in (CALLINGPARTYNO, FINALLYCALLEDPARTYNO)
and 7240 in (CALLINGPARTYNO, FINALLYCALLEDPARTYNO)
and 7358 in (CALLINGPARTYNO, FINALLYCALLEDPARTYNO)
and 7312 in (CALLINGPARTYNO, FINALLYCALLEDPARTYNO);

but I get no records at all

you will have to add multiple clauses. Your SQL engine(database) would optimize it. You can check execution plan for your query. If your values are all numeric value then you can apply range condition. — Acewin
– Acewin, Commented Oct 17, 2017 at 16:13
Regarding your edit: you would need to use OR (not AND); only two (at most) of those # in (x, y) could be true at any given time, and AND requires them to all be true. — Uueerdo
– Uueerdo, Commented Oct 17, 2017 at 16:17
'OR' did not return the accurate results still, but one of the below answers has solved it. Thank you! — Geoff_S
– Geoff_S, Commented Oct 17, 2017 at 16:21
Yeah, that was my answer; OR should've have worked with proper parenthesis; as in ... AND (# in (x,y) OR #2 in (x,y) OR #3 in (x,y) OR ....) — Uueerdo
– Uueerdo, Commented Oct 17, 2017 at 16:47

Gordon Linoff · Accepted Answer · 2017-10-17 16:20:25Z

2

I really think this would more commonly be written as:

where date(ts) = '2017-10-16' and
      (CALLINGPARTYNO in (7276, . . .) or  -- I think the OP wants either one to match
       FINALLYCALLEDPARTYNO in (7276, . . .)
      );

Use and if each should match an extension. Use or if either one needs to match.

answered Oct 17, 2017 at 16:20

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Uueerdo · Accepted Answer · 2017-10-17 16:15:42Z

1

I am fairly certain there is not any specific syntax for checking for "intersections" like that, but this would be simplest.

... AND (callingpartyno IN (the list) OR finallycalledpartyno IN (the list))

Sidenote: Using almost any function in a WHERE condition, like DATE(), will kill your query's performance; ts >= '2017-10-16 00:00:00' AND ts < '2017-10-17 00:00:00' is usually a much better choice.

answered Oct 17, 2017 at 16:15

Uueerdo

16k1 gold badge19 silver badges21 bronze badges

1 Comment

Geoff_S Over a year ago

I'm trying this now, thank you! Also, thanks for the timestamp tip, I'll definitely implement that as well

Serge · Accepted Answer · 2017-10-17 16:22:28Z

1

You can use a subquery:

SELECT *
FROM ambition.session s
  CROSS JOIN (
      SELECT 7276 e UNION
      SELECT 7314 UNION
      SELECT 7295 UNION
      SELECT 7306 UNION
      SELECT 7357 UNION
      SELECT 7200 UNION 
      SELECT 7218 UNION
      SELECT 7247 UNION
      SELECT 7331 UNION 
      SELECT 7255 UNION
      SELECT 7330 UNION
      SELECT 7000 UNION
      SELECT 7215 UNION
      SELECT 7240 UNION
      SELECT 7358 UNION
      SELECT 7312) ext
WHERE date(ts) = '2017-10-16'
AND e.ext IN (CALLINGPARTYNO, FINALLYCALLEDPARTYNO)
;

answered Oct 17, 2017 at 16:22

Serge

4,0662 gold badges20 silver badges39 bronze badges

Collectives™ on Stack Overflow

Checking for multiple values in multiple columns, sql select

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related