January 5, 2012 at 11:14 am
drew.allen (1/4/2012)
ChrisM@home (1/4/2012)
say there are ten rows with the same VND_ID and only one of them matches the filter?You'll need to post sample data illustrating the problem and the desired results.
Drew
Like this:
DROP TABLE #PLU_1
CREATE TABLE #PLU_1 (PLU_1ID INT IDENTITY (1,1), VND_ID INT, DSPL_DESCR VARCHAR(40))
INSERT INTO #PLU_1 (VND_ID, DSPL_DESCR)
SELECT 1, 'quick brown fox jumped over the lazy dog' UNION ALL
SELECT 1, 'quick brown fox jumped over the lazy dog' UNION ALL
SELECT 1, 'quick brown fox jumped over the lazy dog' UNION ALL
SELECT 1, 'quick brown fox jumped over the lazy do"' UNION ALL
SELECT 2, 'quick brown fox jumped over the lazy dog' UNION ALL
SELECT 2, 'quick brown fox jumped over the lazy dog' UNION ALL
SELECT 3, 'quick brown fox jumped over the lazy "' UNION ALL
SELECT 4, 'quick brown fox jumped over the lazy do"'
-- excludes all rows with VND_ID = 1
-- because 1 row found in subselect
SELECT *, replace ([DSPL_DESCR],'"','in')
FROM #PLU_1
where VND_ID NOT IN (
select VND_ID from #PLU_1
Where len(replace ([DSPL_DESCR],'"','in'))>=40
AND DSPL_DESCR LIKE '%"%'
)
It's the original query, but as you figured out, it doesn't fit the requirement.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 5, 2012 at 11:51 am
ChrisM@home (1/5/2012)
drew.allen (1/4/2012)
ChrisM@home (1/4/2012)
say there are ten rows with the same VND_ID and only one of them matches the filter?You'll need to post sample data illustrating the problem and the desired results.
Drew
Like this:
The problem with that data is that it violates database normalization rules, which is entirely possible, but I would only code for that possibility if I could confirm that the database does indeed violate normalization (and curse the original designer if that were the case).
The OP already stated that the version without the subquery solves his problem confirming my assumption that the database was normalized correctly.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 5, 2012 at 12:15 pm
drew.allen (1/5/2012)
ChrisM@home (1/5/2012)
drew.allen (1/4/2012)
ChrisM@home (1/4/2012)
say there are ten rows with the same VND_ID and only one of them matches the filter?You'll need to post sample data illustrating the problem and the desired results.
Drew
Like this:
The problem with that data is that it violates database normalization rules, which is entirely possible, but I would only code for that possibility if I could confirm that the database does indeed violate normalization (and curse the original designer if that were the case).
The OP already stated that the version without the subquery solves his problem confirming my assumption that the database was normalized correctly.
Drew
Actually, I used VND_ID subquery to isolate(not use) the VND_ID whose lenght will increase to more than 40 in the result set...
But then I acheived the same thing by
WHERE LEN(REPLACE(PLU.[DSPL_DESCR], '"', 'in') ) <= 40
Regards,
Skybvi
Regards
Sushant Kumar
MCTS,MCP
January 5, 2012 at 12:44 pm
drew.allen (1/5/2012)
ChrisM@home (1/5/2012)
drew.allen (1/4/2012)
ChrisM@home (1/4/2012)
say there are ten rows with the same VND_ID and only one of them matches the filter?You'll need to post sample data illustrating the problem and the desired results.
Drew
Like this:
The problem with that data is that it violates database normalization rules, which is entirely possible, but I would only code for that possibility if I could confirm that the database does indeed violate normalization (and curse the original designer if that were the case).
The OP already stated that the version without the subquery solves his problem confirming my assumption that the database was normalized correctly.
Drew
You've lost me Drew - I understand that the query I posted is incorrect, but how does the (theoretical) sample data I posted violate normalization? Surely it's just a foreign key?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 5, 2012 at 12:58 pm
ChrisM@home (1/5/2012)
You've lost me Drew - I understand that the query I posted is incorrect, but how does the (theoretical) sample data I posted violate normalization?
I didn't notice that you had set up an identity column.
Surely it's just a foreign key?
Is it? The OP never posted DDL, so it could just as easily be a primary key rather than a foreign key. It's not clear, which is why I asked whether it could be done without the subquery rather than stating outright that it could be done without the subquery.
In fact, since the main query and the subquery use both the same table and the same column, it's more likely to be the primary key than a foreign key.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 5, 2012 at 2:21 pm
This thread illustrates so very well the consequences of not forcing the OP to provide a proper set of requirements before answering ... talented people who could tackle the problem in just one or two posts, given accurate information, are instead left debating what the requirements actually are, many posts later, and wasting their valuable time.
Rant over.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply