How to return multiple rows with repeating non-unique query values in SQL?

Question

I have a table 'mat' with columns x,y,data, where (x,y) is the multi-column primary key, so the table contains data in matrix form. The problem is how to select multiple rows when I have a "vector" of key pairs and there can be repeating pairs:

SELECT x,y,data FROM mat WHERE (x,y) IN ((0,0),(0,0),(1,1));

quite obviously returns

x | y | data
--+---+-----
0 | 0 | 5
1 | 1 | 7

whereas I would need:

x | y | data
--+---+-----
0 | 0 | 5
0 | 0 | 5
1 | 1 | 7

I could loop the key pairs from outside (in c++/whatever code) to get the correct data but there's a major performance degradation and that's quite critical. Any suggestions? Is it possible? Help appreciated!

A Primary Key is unique and cannot contain duplicates. Your {x,y} pairs contain duplicates and cannot be a PK. — joop
– joop, Commented Oct 10, 2017 at 10:28
please either change "primary keys" to smth else in the topic (eg to "values") or remove "non-unique" from it - otherwise it sounds faulty — Vao Tsun
– Vao Tsun, Commented Oct 10, 2017 at 10:36
The primary key itself is unique in the table but I have a data vector of values (x0,y0)...(xn,yn) where there can be duplicates — Streamsoup
– Streamsoup, Commented Oct 10, 2017 at 10:51

Radim Bača · Accepted Answer · 2017-10-10 10:31:37Z

3

I think you need a JOIN for this

SELECT mat.x,mat.y,data 
FROM mat 
JOIN 
(
   SELECT 0 x, 0 y
   UNION ALL
   SELECT 0 x, 0 y
   UNION ALL
   SELECT 1 x, 1 y
) t ON t.x = mat.x and t.y = mat.y

demo

The IN is just evaluated to true/false/unknown for each row, it can not multiply your data.

answered Oct 10, 2017 at 10:31

Radim Bača

10.7k1 gold badge21 silver badges35 bronze badges

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

Comments

Gordon Linoff · Accepted Answer · 2017-10-10 10:42:58Z

1

Radim has the right idea. I prefer this syntax:

SELECT m.*
FROM mat m JOIN 
     (VALUES (0, 0), (0, 0), (1, 1)) v(x, y)
     ON m.x = v.x and m.y = v.y;

answered Oct 10, 2017 at 10:42

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

How to return multiple rows with repeating non-unique query values in SQL?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related