How to select a subset of results from a select statement

Question

I have a table that stores RDF triples:

triples(triple_id, sub_id, pre_id, obj_id)

The method (I need to write) will receive an array of numbers which correspond to pre_id values. I want to select all sub_id values that have a corresponding pre_id for all the pre_ids in the array that is passed in.

E.g. if I had a single pre_id values passed in... lets call the value passed in preId, I would do:

select sub_id from triples where pre_id=preId;

However since I have mutliple pre_id values I want to keep iterating through the pre_id values and only keep the sub_id values corresponding to the "triples" records that have both.

E.g. image there are five records:

triples(1, 34,65,23)
triples(2, 31,35,28)
triples(3, 32,32,19)
triples(4, 12,65,28)
triples(5, 76,32,34)

If I pass in an array of pre_id values [65,32] then I want to select the first, third, fourth and fifth records.

What would I do for that?

what kid of upper limit for the number of pre_id values are we looking at? — BlackICE, Mar 30 '10 at 14:53
Theorectically there can be many ... but in practice usually only be 2-3. I can limit the number if that's required. — Ankur, Mar 30 '10 at 14:57
Looking at it again in the morning, I think I will have to use the subquery method - the IN operator is just another way of creating a series of OR statements, which is not what I need, but useful to know nonetheless. — Ankur, Mar 31 '10 at 03:29

score 5 · Answer 1 · answered Mar 30 '10 at 14:55

5

This might not work with every database, but the keyword in can do this:

select sub_id from triples where pre_id in (65, 32)

answered Mar 30 '10 at 14:55

scherand

2,298
20
27

Doesn't have to be capitalized, but it is good habit to distinguish between what is syntax and what is filteration/etc. – OMG Ponies Mar 30 '10 at 14:58
for (relatively) small sets this will work, for large numbers of pre_id you may need to come up with something different. – BlackICE Mar 30 '10 at 16:07
@David, why won't this work for large number of pre_id. I am constructing the (X,Y,Z) using Java so I can take an arbitrarily large set of pre_ids. Is it a performance issue? – Ankur Mar 31 '10 at 03:26
i'm not saying it *won't* work, but putting large numbers of items as part of an in clause can cause performance issues. – BlackICE Mar 31 '10 at 13:07

score 3 · Answer 2 · edited May 23 '17 at 10:27

You take in the incoming data as a single string, split it by delimiter, and put it into an @Table and use a JOIN, EXISTS or a sub query to get the rows back that you want.

Here is how to split a string into a @Table

T-SQL: Opposite to string concatenation - how to split string into multiple records

Fully working sample in SQL 2008

DROP FUNCTION dbo.Split
go
CREATE FUNCTION dbo.Split (@sep char(1), @s varchar(8000))
RETURNS table
AS
RETURN (
    WITH Pieces(pn, start, stop) AS (
      SELECT 1, 1, CHARINDEX(@sep, @s)
      UNION ALL
      SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
      FROM Pieces
      WHERE stop > 0
    )
    SELECT pn,
      SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
    FROM Pieces
  )
GO
Declare @Triples Table  
(
    triple_id Int, 
    sub_id VarChar (10),  
    pre_id VarChar (10),  
    obj_id VarChar (10)
)

INSERT INTO @Triples  VALUES
(1, 34,65,23),
(2, 31,35,28),
(3, 32,32,19),
(4, 12,65,28),
(5, 76,32,34)

SELECT * 
FROM @Triples Triples
    INNER JOIN dbo.split (',', '65,32') as InputValues
        ON Triples.pre_id = InputValues.S

this is probably the best way to go for large numbers of pre_id being queried upon. — BlackICE, Mar 30 '10 at 16:09

score 1 · Answer 3 · answered Mar 30 '10 at 14:53

1

You can use sub query like this.

select sub_id from triples where pre_id IN (Select pre_id from triples where pre_id <= 65 AND pre_id => 32 )

answered Mar 30 '10 at 14:53

wonde

674
1
8
19

I see... if I limit the number of pre_id values, then I can write four methods, one for each number of subqueries. – Ankur Mar 30 '10 at 14:58
Subquery isn't necessary, you could use BETWEEN instead, and it's not clear that this is the expected behavior. – OMG Ponies Mar 30 '10 at 14:59
1

It did not specify between the two values, it specified specific values. – BlackICE Mar 30 '10 at 16:08
@David this is the query that specifies between values where pre_id <= 65 AND pre_id => 32 – wonde Mar 30 '10 at 16:14

adopilot · Answer 4 · 2010-03-30T15:09:27.623

One of Way you could do it is using Table Value Function whic exists in MSSQL (sory but I do not know for others)

   CREATE FUNCTION [dbo].[fn_ado_test] (@ado nvarchar(4000), @Delim char(1)= ',')
   RETURNS @VALUES TABLE (ado nvarchar(4000),id int)AS
   BEGIN
   DECLARE @chrind INT 
   DECLARE @id int
   DECLARE @Piece nvarchar(4000)

   SELECT @chrind = 1,@id=0
   WHILE @chrind > 0
      BEGIN
         SELECT @chrind = CHARINDEX(@Delim,@ado),@id=@id+1
         IF @chrind > 0
            SELECT @Piece = LEFT(@ado,@chrind - 1)
         ELSE
            SELECT @Piece = @ado

         INSERT @VALUES(ado,id) VALUES(@Piece,@id)
         SELECT @ado = RIGHT(@ado,LEN(@ado) - @chrind)
         IF LEN(@ado) = 0 BREAK
      END
   RETURN
END

After that you use statement like this

/*--For First argument --*/
    select ado from [dbo].[fn_ado_test]('1,2,3',',') as parametar
    where parametar.id=1

/*-- Second --*/
select ado from [dbo].[fn_ado_test]('1,2,3',',') as parametar
        where parametar.id=2

score 0 · Answer 5 · answered Mar 30 '10 at 14:58

0

SELECT sub_id FROM triples WHERE pre_id IN (65, 32)

seems to work for me.

NB: Using MySQL.

answered Mar 30 '10 at 14:58

ryanprayogo

11,587
11
51
66

How to select a subset of results from a select statement

5 Answers5

Linked