SQL Question


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)
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Show us your SELECT statement, and how this 'x' thing works.
andyw27Author Commented:
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'


so the results could be

1 data1
1 data2
1 data3
2 data4
2 data1
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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'...
FROM table 1 t1
   JOIN AnotherTable at ON t1.SomeIDValue = at.SomeIDValue 

Open in new window

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.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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.
andyw27Author Commented:
ok, solved this.  I created a function containing the logic and then just added this into the select statement, worked a treat.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
andyw27Author Commented:
problem solved.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.