andyw27
asked on
SQL Question
Hello,
I have a select statement that accepts a parameter and it can return x results. This works fine for single value, but I need to run it against each row of another table ~20k records.
I could do via C#, however there must be a more efficient way of achieving this via SQL alone?
(I would want it to return a null value where the select statement does not return anything)
I have a select statement that accepts a parameter and it can return x results. This works fine for single value, but I need to run it against each row of another table ~20k records.
I could do via C#, however there must be a more efficient way of achieving this via SQL alone?
(I would want it to return a null value where the select statement does not return anything)
Jim Horn
Show us your SELECT statement, and how this 'x' thing works.
ASKER
Its just a simple select statement,
SELECT * from table 1
where id = @para
if it could change to this:
SELECT * from table 1
where id = '1' or '2' or '3'
etc...
so the results could be
1 data1
1 data2
1 data3
2 data4
2 data1
3 NULL
4 NULL
SELECT * from table 1
where id = @para
if it could change to this:
SELECT * from table 1
where id = '1' or '2' or '3'
etc...
so the results could be
1 data1
1 data2
1 data3
2 data4
2 data1
3 NULL
4 NULL
>but I need to run it against each row of another table ~20k records.
If I'm reading this correctly and '1' or '2' or '3' is in the 'another table'...
Using the JOIN means that it displays the t1 rows ONLY where there is a related row in AnotherTable, and 'related' is defined as having a matching SomeIDValue. Change the names to meet your needs.
If I'm reading this correctly and '1' or '2' or '3' is in the 'another table'...
SELECT t1.*
FROM table 1 t1
JOIN AnotherTable at ON t1.SomeIDValue = at.SomeIDValue Using the JOIN means that it displays the t1 rows ONLY where there is a related row in AnotherTable, and 'related' is defined as having a matching SomeIDValue. Change the names to meet your needs.
I need to run it against each row of another table ~20k records
there's a gadzillion SQL queries that do similar
this is how SQL works natively (over the rows of tables which can be joined together)
I could do via C#
oh no, not in a loop - please no
Q1: what is the first table? (it's easier for you if we know the real names)
Q2: what is the second table? .. ditto ...
Q3: what field in table1 connects to a field in table2?
(could be more than one field in each)
perhaps share with us your existing query? and answers to Q2 & Q3
or: use the generalized answer by Jim if that's sufficient information for you.
there's a gadzillion SQL queries that do similar
this is how SQL works natively (over the rows of tables which can be joined together)
I could do via C#
oh no, not in a loop - please no
Q1: what is the first table? (it's easier for you if we know the real names)
Q2: what is the second table? .. ditto ...
Q3: what field in table1 connects to a field in table2?
(could be more than one field in each)
perhaps share with us your existing query? and answers to Q2 & Q3
or: use the generalized answer by Jim if that's sufficient information for you.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
problem solved.