September 15, 2015 at 3:59 pm
Hi All,
Lets say I have a set of columns as follows:
Table.Name - Table.ID - Table.Code
I want to write a query that will cycle through the results and if it comes across another record that has a matching Table.ID I want to exclude that row from the result set.
I am not all too familiar with how to use either a Case or If..Else Statement within a Sql statement that would accomplish this. Any suggestions or advise would be greatly appreciated.
September 15, 2015 at 4:05 pm
I'm not to good at theoretical queries based on partial information. Plus, the question as posed, generates more questions which could be answered by clicking on the link in my signature and posting the appropriate ddl and consumable data.
It will make it much easier to answer your question.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 15, 2015 at 4:24 pm
If I'm understanding what you want, you might try doing a CTE:
;WITH IDRank AS (
SELECT t.Name,
t.ID,
t.Code,
ROW_NUMBER() OVER(PARTITION BY t.ID ORDER BY t.Code) AS RankID
FROM dbo.[Table] t
)
Then select all rows with RankID = 1. But like LinksUp said, it's much easier to give a good solution if you provide more complete information.
September 17, 2015 at 12:16 pm
Thanks for the responses, yep as I read it back it seems a bit vague, here is the setup:
I have two tables, I want to exclude entries from the 1st table that show a match in the 2nd table. To shorten the list of rows in the 1st table to only those that do not exist in the second table.
For example here is a query I use to pull data (ID Column) from "Table B" along with it's results (Im filtering on one name for this example joining a 3rd table that contains identifiers):
SELECT
TableA.last_name,
TableA.first_name,
TableB.ID
FROM
TableA
INNER JOIN TableB on TableA.id = TableB.id
WHERE
TableA.first_name='Denise'
and TableA.last_name='Test'
Test Denise 285.22
Test Denise 453.40
Test Denise 401.1
Test Denise 174.4
Test Denise 585.9
Test Denise 276.51
Test Denise 174.4
Test Denise V86.0
Test Denise 174.0
Here is the 2nd set of query results which contains the data I would exclude from the results of the first query using TableB:
Select
TableA.last_name,
TableA.first_name,
TableC.ID
from
TableA
INNER JOIN TableC on TableA.id = TableC.id
TableA.first_name='Denise'
and TableA.last_name='Test'
Test Denise 372064008
Test Denise 395557000
Test Denise C50.412
Test Denise Z17.0
Test Denise 188154003
Test Denise C50.011
Test Denise 174.0
Test Denise V86.0
Test Denise 174.4
So from the results of these two queries you can see that the 2nd set of query results pulling the ID from TableC also includes the values 174.4, V86.0 and 174.0 referenced in TableB from the first query. So what I want to be able to do is generate a query that will remove those rows from the first table so that I'll just have the remaining results, which in this case would be 5 rows left over:
Test Denise 285.22
Test Denise 453.40
Test Denise 401.1
Test Denise 585.9
Test Denise 276.51
Any thoughts on the best way to do this? At the end of the day I want to remove that filter I have on one name so that it will go through thousands of names and cycle through both tables B and C to exclude what is necessary and return the remaining results I need from Table B.
September 17, 2015 at 12:37 pm
Here is one solution:
SELECT
ta.last_name,
ta.first_name,
tb.ID
FROM
TableA ta
INNER JOIN TableB tb
on ta.id = tb.id
WHERE
ta.first_name='Denise'
and ta.last_name='Test'
EXCEPT
SELECT
ta.last_name,
ta.first_name,
tc.ID
FROM
TableA ta
INNER JOIN TableC tc
on ta.ID = tc.ID
WHERE
ta.first_name='Denise'
and ta.last_name='Test';
September 17, 2015 at 4:08 pm
Thank you Lynn,
That worked perfectly without having to run for too long. Took only 7 seconds to return 250k records, I can deal with that.
Now I need to move on to figuring out how to get this dataset to render in SSRS using parameters without bogging things down.lol
September 18, 2015 at 2:20 pm
Here's another method to try, just in case:
SELECT
ta.last_name,
ta.first_name,
tb.ID
FROM
TableA ta
INNER JOIN TableB tb
on ta.id = tb.id
LEFT OUTER JOIN TableC tc
on ta.ID = tc.ID
WHERE
ta.first_name='Denise'
and ta.last_name='Test'
and tc.ID IS NULL
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 19, 2015 at 2:39 am
al3408 (9/17/2015)
Took only 7 seconds to return 250k records
If you "retrieved" them? that might pretty much all be display time.
Perhaps measure the time of:
SELECT JustThePKeyColumn
INTO #TEMP
FROM ...
which will store the results into a temporary table, so is representative of "doing something" with the data, whereas displaying it is representative of how quickly, e.g. SSMS :), can do that job. Or you could do
DECLARE @DummyVariable varchar(99) -- needs to be the same type as the KPey column
SELECT @DummyVariable = ThePKeyColumn
FROM ...
this will "throw away" the results so you are then pretty much just timing the Query itself. That's probably the best way if you have two or three methods to compare (performance).
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply