how to filter data from storeprocedure

Question

how to filter data from storeprocedure

simisabu

SSC Rookie

Points: 35
More actions
March 18, 2008 at 12:51 am

#185294

i have a storeprocedure to get details from several atbles of items. i used inner join to get all collection of data . but my datas are all repeating since in one table i have a mode field which gets the same pkid. is there any way that i can get more filter from my store procedure without neglecting the inner join of that table.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Praveen Krishna R SSC Enthusiast Points: 150 More actions · Answer 1

Hi simisabu,

You can put the distinct values from the first table into a temporary table, and then you can inner join the second table with this temporary table.

Thus avoiding the duplicate rows.

Eg.

SELECT DISTINCT PKID

INTO #TEMPFIRSTTABLE

FROM FIRSTTABLE

SELECT *

FROM #TEMPFIRSTTABLE

INNER JOIN SECONDTABLE

ON [SOME COLUMN] = [SOME OTHER COLUMN]

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 2

If you're getting multiple values from one table, you should probably apply some type of filter either in the ON statement of the join or in the WHERE clause. You said that the table had multiple types of values for the foreign key in question. Is there one of these types that will always be present in your results? Then use that in the ON statement of the join:

SELECT...

FROM... x

INNER JOIN y

ON x.Id = y.Id

AND y.Type = 42

While DISTINCT will solve your immediate issue, it can cause performance problems and mask data integrity problems. It should be used very sparingly, if at all.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Use derived tables in preference to temporary tables, and as Grant states, avoid using DISTINCT:

SELECT *

FROM FIRSTTABLE s

INNER JOIN (SELECT PKID

FROM SECONDTABLE

GROUP BY PKID) d

ON d.PKID = s.PKID

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden