For each row in a table return all rows containing value +21 (by using SQL)

Question

It is seemingly easy, but I am stucked here. I am using VS2010, C# environment.

So, basically, we have database containing table "masses". This table contains column "mass" (value type is double). I must, for each row, find all records in which "mass" equals value of "mass" in specific row PLUS 21. And put these resulting records into an existing table in same database.

Could it be done by SQL or I must use iteration to do this ?

For making it clear... Where are you planning to do your "for-each-row"? C# side or SQL side??? — Adrian Salazar, Jan 30 '13 at 12:13
Yes, it can be done with pure SQL easily, some examples here: http://stackoverflow.com/questions/4101739/sql-server-select-into-existing-table — mattmanser, Jan 30 '13 at 12:14
@JosipMiller From experience, rarely should you need to do anything row by row by iterating - that's not what databases are designed for! — Bridge, Jan 30 '13 at 12:23
Thanks for the tip. :) I guess there is much more SQL to be learned. — Josip Miller, Jan 30 '13 at 12:50
@JosipMiller can you explain better " I must, for each row, find all records in which "mass" equals value of "mass" in specific row PLUS 21" please? — Adrian Salazar, Jan 30 '13 at 21:30

Bridge · Answer 1 · 2013-01-30T17:00:44.047

1

You could do this using an in statement.

INSERT INTO SomeOtherTable (<columns you want>)
SELECT <columns you want>
FROM   Masses
WHERE  Mass IN (SELECT mass + 21
                FROM   masses)

Alternatively this could be written using EXISTS:

INSERT INTO SomeOtherTable (<columns you want>)
SELECT <columns you want>
FROM   Masses
WHERE  EXISTS
(
    SELECT NULL
    FROM   masses M
    WHERE  Masses.Mass = M.Mass + 21 
)

Or using a derived table:

INSERT INTO SomeOtherTable (<columns you want>)
SELECT <columns you want>
FROM   Masses
       INNER JOIN (SELECT Mass + 21 AS NewMass
                   FROM   masses) AS NewMasses
         ON NewMasses.NewMass = Masses.Mass

edited Jan 30 '13 at 17:00

answered Jan 30 '13 at 12:12

Bridge

29,818
9
60
82

I did not try with "Declare" Keyword but I will. Thanks ! – Josip Miller Jan 30 '13 at 12:19
That means that we must establish values that are +21 for EACH record in the table. – Josip Miller Jan 30 '13 at 12:26
@JosipMiller Please expain further. Are you looking to update every record, adding 21 to each mass? – Bridge Jan 30 '13 at 12:30
No update in the table "masses". We need to "search" the table "masses" for value "mass +21" for EACH row. For example, if in row 2 mass= 22 then we search for row in table that has "mass" value of 43. And we do it for EACH row. After that we put records we find into an existing table. – Josip Miller Jan 30 '13 at 12:37
This looks very elegant, thanks. :) In same application I already used JOINS to search for data but now I did not consider this method for some reason. And seems to me that it can be the solution to the problem. OK, I will try it and post back to you. – Josip Miller Jan 30 '13 at 12:47
@JosipMiller I've edited some more - this way should be much simpler. – Bridge Jan 30 '13 at 12:48
OK, I'll compare these two solution. Tnx. – Josip Miller Jan 30 '13 at 12:58
this does not handle a specific row - also there is unnecessary use of the IN clause. – user1731782 Jan 30 '13 at 13:32
@user1731782 Read what Josip said the clarification comment above - `for EACH row`. I can hardly be blamed if Josip asks for different things between the original question, and in the comments. Also please explain what you mean by `unnecessary use of the IN clause` - it's completely necessary if Josip does not want to narrow to a specific row. – Bridge Jan 30 '13 at 14:21
@bridge I have read it. He says a specific row PLUS 21. My 1st nested SELECT will return multiple rows. Your use of an IN clause (which you have since removed) is less efficient than comparison with a single double. For what its worth, the use of the EXISTS and the INNER JOIN is also less efficient than the answer I proposed. Do a few timing comparisons if you don't believe me. – user1731782 Jan 30 '13 at 19:29
@bridge Further, whilst correct, you're solutions a needlessly messy and less efficient than the simplist answer. You shouldn't vote my answer down for this, but use it as an opportunity to learn. – user1731782 Jan 30 '13 at 19:31
@user1731782 I haven't removed the use of an in clause, that's the first part of this answer. I tested your solution on SQL fiddle, and the plan shows me it transformed it into an inner join too (see [this](http://sqlfiddle.com/#!3/cb391/5)). It still doesn't change the fact that our two queries are designed for different things. With OP's comment above, it infers that he wants to "search" for the mass + 21 for a specific row's value, but then goes on to say `And we do it for EACH row.`. Why do this operation for a single value for each row, when it could be done with sets to begin with? – Bridge Jan 30 '13 at 23:53
Oh and the query optimiser seems to optimise both my in statement solution and the exists solution to the same thing, if you're interested. – Bridge Jan 30 '13 at 23:55
Hello, Bridge. I tried to implement the method with IN clause but it always returns empty recordset. However, I will try your method by using JOIN. I'll post back about results. – Josip Miller Jan 31 '13 at 09:30
@JosipMiller The results should be the same for each. Can you please post the column definition (precision and scale)? – Bridge Jan 31 '13 at 09:41
Brigde, thank you a lot (and everybody else) for help. JOIN method works. It returns the rows I inserted on purpose with value +21. I am not sure why "IN" solution returns empty recordset (probably I made error somewhere) but JOIN works. JOIN also should be faster, I guess ? – Josip Miller Jan 31 '13 at 09:53
@JosipMiller One way to find out - and that's test :-) – Bridge Jan 31 '13 at 09:57
Of course. :) Later on, I'll check how solution with IN clause works, now I am a bit stuck with time. More precisely, this situation is part of application for comparison of combinations of molecular masses in complex mixtures, so it works with "mass" values mostly of "double" type. Application was working but my CEO required some additional functionality. – Josip Miller Jan 31 '13 at 10:26

Adrian Salazar · Answer 2 · 2013-01-30T13:51:09.357

You can iterate through the table using a cursor. The cursor can allocate the value(s) from the column(s) in the row to SQL variables. Then for every row you make your filtering and then you make whatever you want with the results.

-- Create a cursor that iterates through the table and only reads the 'mass' column
DECLARE C1 CURSOR FOR 
SELECT mass FROM masses
DECLARE @current decimal

OPEN C1

-- Copy the value from the 'mass' column to a vaiable
FETCH NEXT FROM C1 INTO @current

WHILE @@fetch_status = 0
BEGIN

    -- Select the rows that have mass = current row mass + 21
    SELECT * -- This ca be improved by selecting only the rows you need
    FROM masses
    WHERE mass = @current + 21

    FETCH NEXT FROM C1 INTO @current
END
CLOSE C1
DEALLOCATE C1

In the nested select you can then copy all the rows to your destination table, e.g. by changing this lines:

INSERT INTO DESTINATION_TABLE(row1, row2, ..., rown)
SELECT (row1, row2, ..., rown)
FROM masses
WHERE mass = @current + 21

Thank you for the answer. I must study this method a bit but looks that might help. — Josip Miller, Jan 30 '13 at 12:40
You're welcome. I'll comment the code a little more to make it easier to understand. — Adrian Salazar, Jan 30 '13 at 13:06
Wow! Bit of a sledgehammer to crack a nut here. Problem can be solved with some simple SQL. — user1731782, Jan 30 '13 at 19:34

For each row in a table return all rows containing value +21 (by using SQL)

2 Answers2