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 get an array of numbers that matches the pre_id values. I want to select all sub_id values ​​that have the corresponding pre_id for all pre_ids in the array that is being passed.

eg. if I had one pre_id value passed in ... allows you to 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 continue to iterate through the pre_id values ​​and save the sub_id values ​​corresponding to the “triple” records that both have.

eg. image there are five entries:

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 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 this?

Answer 1

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

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

Answer 2

You take the incoming data as one line, separate it with a separator and put it in @Table and use JOIN , EXISTS or sub query to get the necessary lines.

Here's how to split a string on @Table

T-SQL: against string concatenation - how to split a 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 

Answer 3

You can use a sub request like this.

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

Answer 4

One way you could do this is to use the table value function that exists in MSSQL (sory, but I don't know 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 such an operator

 /*--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 

Answer 5

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

seems to work for me.

NB: using MySQL.