April 17, 2012 at 12:35 pm
Hi,
I have this problem:
this is the situation.
table one:
|Time |Text|
08:30 'some text'
08:30 'some text'
08:30 'some text'
08:30 'some text'
09:00 'some text'
table Two:
|Time |Text|
08:30 'some text'
08:30 'some text'
I want to select values from table one filtering out the values from table two. So that I get a new selection filtering out the duplicates which is here the time column.
I want to to have this result:
RESULT:
|Time |Texst|
08:30 'some text'
08:30 'some text'
09:00 'some text'
Is this possible in tsql with a join? or with something else?
I appreciate your help. Thanks.:-D
c# .NET developer, TSQL
April 17, 2012 at 12:40 pm
How do you expect to join these tables? for your expected results, why don't the first 4 rows of table one match the first 2 of table 2?
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 17, 2012 at 12:51 pm
Hi,
thanks for your fast reply.
The thing I am trying to filter out are the first 2 rows from table one. Because the first two rows are in table two they have to be removed from table one. So if for example in the second table there were 3 rows with same time they have to be filterd out from table one, I mean deleted from table one. That's what I am trying to do.
I hope I am clear if not I'll send you some example:-D
cheers
c# .NET developer, TSQL
April 17, 2012 at 12:54 pm
Some examples where it doesn't apear that everything is duplicated would work nicely. In your OP it appears that the 8:300 entries are all the same in both tables.
April 17, 2012 at 1:09 pm
Please guys
Just help me out here with an example query or maybe it is not possible what I am asking?
Mo,:Whistling:
c# .NET developer, TSQL
April 17, 2012 at 1:21 pm
The_Mo (4/17/2012)
Please guysJust help me out here with an example query or maybe it is not possible what I am asking?
Mo,:Whistling:
Help us help you, read the article Forum Etiquette: How to post data/code on a forum to get the best help[/url]. Follow the instructions provided in that article and give us the DDL for your table(s), sample data for the tables, expected results, and the code you are having problems with.
April 17, 2012 at 1:22 pm
It's possible, but you have to answer our questions. The data sets look the same. If we compare table 1 to table 2, all the 8:30 entries are the same and will be removed. Can you provide the DDL and sample data as well as expected results?
Sorry Lynn, you beat me to it 🙂
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 17, 2012 at 2:14 pm
I can't give you specifics because your question doesn't include any concrete examples that match your question.
However, the general approach is to outer join the two tables on some condition and filter WHERE <SomeColumn> IS NOT NULL. This will filter out matching rows between the two tables.
Filtering out the duplicates will also depend on your DDL and sample output, by defining what your key is or what will identify a duplicate value.
The specific column(s) and exact query will depend on the DDL and sample output you provide.
April 17, 2012 at 2:37 pm
Ok guys,
Thank you. It's clear. I'll try to be more specific. I am a newbie you know :blush:
By the way what does DDL stand for? what does it mean?
Tommorow I'll have the complete question ready 😎
Thank you.
c# .NET developer, TSQL
April 17, 2012 at 2:44 pm
April 18, 2012 at 11:09 am
The_Mo (4/17/2012)
By the way what does DDL stand for? what does it mean?
DDL stands for Data Definition Language
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 24, 2012 at 4:59 am
hey guys,
sorry for the delay, But here is the code 😎
--===== table one
CREATE TABLE #tableOne
(
DateValue DATETIME,
Value varchar(50)
)
INSERT INTO #tableOne(DateValue,Value) VALUES('2012-04-29 08:30:00','SOME TEXT');
INSERT INTO #tableOne(DateValue,Value) VALUES('2012-04-29 08:30:00','SOME TEXT');
INSERT INTO #tableOne(DateValue,Value) VALUES('2012-04-29 08:30:00','SOME TEXT');
INSERT INTO #tableOne(DateValue,Value) VALUES('2012-04-29 09:00:00','SOME TEXT');
INSERT INTO #tableOne(DateValue,Value) VALUES('2012-04-29 16:00:00','SOME TEXT');
--===== table two
CREATE TABLE #tableTwo
(
DateValue DATETIME,
Value varchar(50)
)
INSERT INTO #tableTwo(DateValue,Value) VALUES('2012-04-29 08:30:00','SOME TEXT');
INSERT INTO #tableTwo(DateValue,Value) VALUES('2012-04-29 08:30:00','SOME TEXT');
INSERT INTO #tableTwo(DateValue,Value) VALUES('2012-04-29 09:00:00','SOME TEXT');
INSERT INTO #tableTwo(DateValue,Value) VALUES('2012-04-29 10:00:00','SOME TEXT');
--===== table result
CREATE TABLE #tableResult
(
DateValue DATETIME,
Value varchar(50)
)
INSERT INTO #tableResult(DateValue,Value) VALUES('2012-04-29 08:30:00','SOME TEXT');
INSERT INTO #tableResult(DateValue,Value) VALUES('2012-04-29 16:00:00','SOME TEXT');
select * from #tableResult
drop TABLE #tableOne
drop TABLE #tableTwo
drop TABLE #tableResult
I hope this time it is clear.
Cheers!
c# .NET developer, TSQL
April 24, 2012 at 5:26 am
You have placed '2012-04-29 08:30:00','SOME TEXT' into your expected results, but it is present in #tableTwo?
What it makes to be picked up for the result?
Is it just because this record in #tableOne appears three times but only two times in the #tableTwo?
April 24, 2012 at 6:23 am
April 24, 2012 at 6:34 am
maby this?? since we need some way to make them distinct add a row number to it then do a full outer join. now only the differences will make it through the query.
SELECT * FROM
(SELECT ROW_NUMBER () OVER (PARTITION BY DateValue,Value ORDER BY (SELECT NULL)) RowNum, *
FROM #temp1) t1
FULL OUTER JOIN
(SELECT ROW_NUMBER () OVER (PARTITION BY DateValue,Value ORDER BY (SELECT NULL)) RowNum, *
FROM #temp2) t2
ON t1.DateValue = t2.DateValue
AND t1.Value = t2.Value
AND t1.RowNum = t2.RowNum
WHERE t1.RowNum IS NULL
OR t2.RowNum IS NULL
EDIT: this will work if the entries are exactly the same like your sample data. however if your sample data is not correct then neither is this answer.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply