how to compare with T-SQL

Hello,
I have a list of employees (+5000) that I use to compare with a database.
I can find the employees that match the list.
How can I find the ones that are not in the database from this employee
list?
For example,
This is the Employee List (text file)
EmployeeID LastName FirstName
2 Fuller Andrew
3 Leverling Janet
11 Tool Mike
13 Oneal Jeff
Use Northwind
Select EmployeeID, LastName, FirstName
From Employees
Where EmployeeID IN
(
2,
3,
11,
13
)
Result:
2 Fuller Andrew
3 Leverling Janet
I do not know how to display the ones that don't match.
EmployeeID 11 and 13.
Expected Resut of the query would be:
11 Tool Mike
13 Oneal Jeff
Please help...Thanks a bunch.Select * from EmployeeTable
Where EmployeeID not in
(
Select EmployeeID from OtherEmployeeTable
)
If you want to display all Employees, even those who are not in the second
list
Select ET.EmployeeID, ISNULL(OET.EmployeeID,'That ones missing in the second
Table' from EmployeeTable ET
Left Join OtherEmployeeTable
ON
ET.EmployeeID = OET.EmployeeID
HTH, Jens Suessmeyer.
"SQL Apprentice" <mssqlworld@.yahoo.com> schrieb im Newsbeitrag
news:uABB83ZUFHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a list of employees (+5000) that I use to compare with a database.
> I can find the employees that match the list.
> How can I find the ones that are not in the database from this employee
> list?
> For example,
> This is the Employee List (text file)
> EmployeeID LastName FirstName
> 2 Fuller Andrew
> 3 Leverling Janet
> 11 Tool Mike
> 13 Oneal Jeff
> Use Northwind
> Select EmployeeID, LastName, FirstName
> From Employees
> Where EmployeeID IN
> (
> 2,
> 3,
> 11,
> 13
> )
> Result:
> 2 Fuller Andrew
> 3 Leverling Janet
> I do not know how to display the ones that don't match.
> EmployeeID 11 and 13.
> Expected Resut of the query would be:
> 11 Tool Mike
> 13 Oneal Jeff
> Please help...Thanks a bunch.
>|||SQL Apprentice wrote:
> Hello,
> I have a list of employees (+5000) that I use to compare with a
> database. I can find the employees that match the list.
> How can I find the ones that are not in the database from this
> employee list?
> For example,
> This is the Employee List (text file)
> EmployeeID LastName FirstName
> 2 Fuller Andrew
> 3 Leverling Janet
> 11 Tool Mike
> 13 Oneal Jeff
> Use Northwind
> Select EmployeeID, LastName, FirstName
> From Employees
> Where EmployeeID IN
> (
> 2,
> 3,
> 11,
> 13
> )
> Result:
> 2 Fuller Andrew
> 3 Leverling Janet
> I do not know how to display the ones that don't match.
> EmployeeID 11 and 13.
> Expected Resut of the query would be:
> 11 Tool Mike
> 13 Oneal Jeff
> Please help...Thanks a bunch.
You cannot do this with a static list. You need to have a table, either
physical or derived. Here is an example using a derived table:
Select ListItem
From (
SELECT 2 AS ListItem
UNION ALL
SELECT 3
UNION ALL
SELECT 11
UNION ALL
SELECT 13) l LEFT JOIN Employees e
ON ListItem = EmployeeID
WHERE EmployeeID IS Null
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Thanks for the good advice,
I imported the text file(employee list) into another table and did a
compare.
"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:e0R1UMaUFHA.3944@.tk2msftngp13.phx.gbl...
> SQL Apprentice wrote:
> You cannot do this with a static list. You need to have a table, either
> physical or derived. Here is an example using a derived table:
> Select ListItem
> From (
> SELECT 2 AS ListItem
> UNION ALL
> SELECT 3
> UNION ALL
> SELECT 11
> UNION ALL
> SELECT 13) l LEFT JOIN Employees e
> ON ListItem = EmployeeID
> WHERE EmployeeID IS Null
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>

how to compare what has been changed between yesterday and today

Hello
1/ I am looking for ways to find out what has been changed per each table.
2/ Everyday I have a full database back up bak file created - about 10G.
(about 400 tables)
3/ Every day I will do a full restore of the day before yesterday database
and yesterday database.
e.g. Name of databases: DayBeforeYestDB,yesterdayDB
4/ Question:
Any tools to generate created another database that will contain only the
list of tables with data changed.
or
do I have to write my own scripts
Regards
it com
www.red-gate.com
SQL Data Compare...best $295 you will ever spend...
Kevin Hill
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"itcom" <jensonluk1@.netscape.net> wrote in message
news:B5163A1E-880F-4437-AE2C-AFACA43949CD@.microsoft.com...
> Hello
> 1/ I am looking for ways to find out what has been changed per each table.
> 2/ Everyday I have a full database back up bak file created - about 10G.
> (about 400 tables)
> 3/ Every day I will do a full restore of the day before yesterday database
> and yesterday database.
> e.g. Name of databases: DayBeforeYestDB,yesterdayDB
> 4/ Question:
> Any tools to generate created another database that will contain only the
> list of tables with data changed.
> or
> do I have to write my own scripts
>
> Regards
> --
> it com
|||Hi,
What actually u need to know .If i understand ur Ques. u want that what
data chnages took place between last 2 backups.
Red-gate is good it will set a auditing option so will come to know
what datachanges and in which table.
Otherwise u can write ur on script.
U can restore both the database and compare the record count.
Or enable a C2 Auditing mode.
from
Doller

how to compare what has been changed between yesterday and today

Hello
1/ I am looking for ways to find out what has been changed per each table.
2/ Everyday I have a full database back up bak file created - about 10G.
(about 400 tables)
3/ Every day I will do a full restore of the day before yesterday database
and yesterday database.
e.g. Name of databases: DayBeforeYestDB,yesterdayDB
4/ Question:
Any tools to generate created another database that will contain only the
list of tables with data changed.
or
do I have to write my own scripts
Regards
it comwww.red-gate.com
SQL Data Compare...best $295 you will ever spend...
Kevin Hill
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"itcom" <jensonluk1@.netscape.net> wrote in message
news:B5163A1E-880F-4437-AE2C-AFACA43949CD@.microsoft.com...
> Hello
> 1/ I am looking for ways to find out what has been changed per each table.
> 2/ Everyday I have a full database back up bak file created - about 10G.
> (about 400 tables)
> 3/ Every day I will do a full restore of the day before yesterday database
> and yesterday database.
> e.g. Name of databases: DayBeforeYestDB,yesterdayDB
> 4/ Question:
> Any tools to generate created another database that will contain only the
> list of tables with data changed.
> or
> do I have to write my own scripts
>
> Regards
> --
> it com|||Hi,
What actually u need to know .If i understand ur Ques. u want that what
data chnages took place between last 2 backups.
Red-gate is good it will set a auditing option so will come to know
what datachanges and in which table.
Otherwise u can write ur on script.
U can restore both the database and compare the record count.
Or enable a C2 Auditing mode.
from
Doller

how to compare what has been changed between yesterday and today

Hello
1/ I am looking for ways to find out what has been changed per each table.
2/ Everyday I have a full database back up bak file created - about 10G.
(about 400 tables)
3/ Every day I will do a full restore of the day before yesterday database
and yesterday database.
e.g. Name of databases: DayBeforeYestDB,yesterdayDB
4/ Question:
Any tools to generate created another database that will contain only the
list of tables with data changed.
or
do I have to write my own scripts
Regards
--
it comwww.red-gate.com
SQL Data Compare...best $295 you will ever spend...
--
Kevin Hill
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"itcom" <jensonluk1@.netscape.net> wrote in message
news:B5163A1E-880F-4437-AE2C-AFACA43949CD@.microsoft.com...
> Hello
> 1/ I am looking for ways to find out what has been changed per each table.
> 2/ Everyday I have a full database back up bak file created - about 10G.
> (about 400 tables)
> 3/ Every day I will do a full restore of the day before yesterday database
> and yesterday database.
> e.g. Name of databases: DayBeforeYestDB,yesterdayDB
> 4/ Question:
> Any tools to generate created another database that will contain only the
> list of tables with data changed.
> or
> do I have to write my own scripts
>
> Regards
> --
> it com|||Hi,
What actually u need to know .If i understand ur Ques. u want that what
data chnages took place between last 2 backups.
Red-gate is good it will set a auditing option so will come to know
what datachanges and in which table.
Otherwise u can write ur on script.
U can restore both the database and compare the record count.
Or enable a C2 Auditing mode.
from
Doller

How to compare values in different rows?

Hi

I’ve a table like this (in SQL Server 2000).

v1 v2
2 8
7 10
11 15

13 17

v1 value of a row must be grater than v2 value of previous row, other wise I need to display that row, in above example, I need to display 2nd & 4th rows.

please advise

Thanks

In order to best get this to work you need another row to explicitly sequence the data -- such as ROW_ID or something. The otherwise, you can use Transact SQL extensions, but without an explicit sequence of some kind, the results will be volatile / unpredictable. It would probably be best to avoid the extensions and stick to a CTE here if you can.

Code Snippet

declare @.ex table(id int, v1 int, v2 int)
insert @.ex
select 1, 2, 8 union all
select 2, 7, 10 union all
select 3, 11,15 union all
select 4, 13,17
--select * from @.ex

select a.id, a.v1, a.v2
from @.ex a
join @.ex b
on a.id - 1 = b.id
and a.v1 <= b.v2

/*
id v1 v2
-- -- --
2 7 10
4 13 17
*/

|||

Do you have a field in the table that will indicate what is the 'previous row'? (Something like an IDENTITY field, or datetime of entry, etc.)

|||

The Table is

ID v1 v2
1 2 8
2 7 10
3 11 15

4 13 17

|||

Here it is,

Code Snippet

Create Table #data (

[ID] INT ,

[v1] INT ,

[v2] INT

);

Insert Into #data Values('1','2','8');

Insert Into #data Values('2','7','10');

Insert Into #data Values('3','11','15');

Insert Into #data Values('4','13','17');

Select

Down.*

from

#data Up

Join #data down On Up.Id = Down.Id-1

Where

Up.V2>=down.V1

|||

As long as you have that ID field, you can use the query

Code Snippet

CREATE TABLE prevRowTest (

ID INT,

v1 INT,

v2 INT

)

INSERT INTO prevRowTest VALUES (1,2,8)

INSERT INTO prevRowTest VALUES (2,7,10)

INSERT INTO prevRowTest VALUES (3,11,15)

INSERT INTO prevRowTest VALUES (4,13,17)

SELECT prt.ID, prt.v1, prt.v2

FROM prevRowTest prt JOIN

prevRowTest prev ON prt.id = (prev.ID + 1)

WHERE prt.v1 <= prev.v2

|||

Thanks a lot.

I got my Answer

With Regards

Vijay

How to compare value pair of one table with value pair of another table

Here is the problem:
--------
create table A( a1 int, a2 int)
create table B( b1 int, b2 int)

insert A values(1,1)
insert A values(1,2)
insert A values(2,1)
insert A values(2,3)
insert A values(3,1)
insert A values(3,3)

insert B values(1,1)
insert B values(1,2)
insert B values(3,2)
insert B values(2,2)

What is the SQL query to find out the pairs existing in B, but not in A ?

I can solve this by using string functions, but my problem is I have to write a query that can execute on MSSQL, DB2 and ORACLE, and as you know they have differnt syntax for string functions.

Thanks in advance.

Regards,
s99shah.pairs in B but not in A? left outer join, check for unmatched rows --
select b1, b2
from B
left outer
join A
on b1 = a1
and b2 = a2
where a1 is null

rudy
http://r937.com/|||Hi,

TRY THIS

Select B.* from a,b
where (+) a1 = b1;

how to compare value of two fileds and based on that insert value into third fileds

Hi,
I have a database with table name as test in that i have 6 colums
they are
name varchar (20)
address varchar (20)
position varchar (20)
nametype1 varchar (20)
nametype2 varchar (20)
nameval varchar(20)
now in the nametype1 and nametype2 there are values like
nametype1 nametype2
"AB" "BA"
"BB" "BB"
"AA" "AA"
"BA" "AB"
now depending upon the combination i want to assign value to the thrid
field that is nameval like example below
nametype1 nametype2 nameval
"AB" "BA" 1
"BB" "BB" 2
"AA" "AA" 2
"BA" "AB" 1
please suggest query in sql which i can run to do this .
Regards
On Jan 30, 3:52 pm, "Dejan Sarka"
<dejan_please_reply_to_newsgroups.sa...@.avtenta.si > wrote:[vbcol=seagreen]
> You can use a computed column. Check this script:
> CREATE TABLE dbo.t1
> (nametype1 char(2),
> nametype2 char(2),
> nameval AS
> CASE
> WHEN nametype1 = nametype2 THEN 2
> WHEN nametype1 = REVERSE(nametype2) THEN 1
> ELSE 0 -- error?
> END)
> GO
> INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('AB','BA')
> INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('BB','BB')
> INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('AA','AA')
> INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('BA','AB')
> SELECT *
> FROM dbo.t1
> --
> Dejan Sarkahttp://www.solidqualitylearning.com/blogs/
> "Tradeorganizer" <tradeorgani...@.gmail.com> wrote in message
> news:1170146292.237194.199580@.q2g2000cwa.googlegro ups.com...
>
>
>
>
instead of creatating in new table can i update the value in
existing table please suggest.
|||UPDATE t1 SET nameval =
CASE WHEN nametype1 = nametype2 THEN 1
WHEN nametype1 = REVERSE(nametype2) THEN 2
END
Regards
Amish Shah
http://shahamishm.tripod.com
On Jan 30, 4:16 pm, "Tradeorganizer" <tradeorgani...@.gmail.com> wrote:
> On Jan 30, 3:52 pm, "Dejan Sarka"
>
>
> <dejan_please_reply_to_newsgroups.sa...@.avtenta.si > wrote:
>
>
>
>
>
>
>
> instead of creatating in new table can i update the value in
> existing table please suggest.- Hide quoted text -
> - Show quoted text -
|||On Jan 30, 5:33 pm, "amish" <shahami...@.gmail.com> wrote:[vbcol=seagreen]
> UPDATE t1 SET nameval =
> CASE WHEN nametype1 = nametype2 THEN 1
> WHEN nametype1 = REVERSE(nametype2) THEN 2
> END
> Regards
> Amish Shahhttp://shahamishm.tripod.com
> On Jan 30, 4:16 pm, "Tradeorganizer" <tradeorgani...@.gmail.com> wrote:
>
>
>
>
>
>
>
>
>
hi i have an update to the query , please suggest if the table
structure and results are below then what should i run for no of
fileds
name varchar (20)
address varchar (20)
position varchar (20)
nametype1 varchar (20)
nametype2 varchar (20)
nametype3 varchar(20)
nametype4 varchar(20)
nameval varchar(20)
nameval1 varchar(20)
nameval2 varchar(20)
nameval3 varchar(20)
now in the nametype1 and nametype2 there are values like
nametype1 nametype2 nametype3 nametype4
"AB" "BA" "BB" "BB"
"AA" "AA" "BA" "AB"
"AB" "BA" "BB" "BB"
"AA" "AA" "BA" "AB"
now depending upon the combination i want to assign value to the thrid
field that is nameval like example below
nametype1 nametype2 nameval
"AB" "BA" 1
"AA" "AA" 2
"AB" "BA" 1
"AA" "AA" 2
nametype1 nametype3 nameval1
"AB" "BB" 1
"AA" "BA" 1
"AB" "BB" 1
"AA" "BA" 1
nametype1 nametype4 nameval2
"AB" "BB" 1
"AA" "AB" 1
"AB" "BB" 1
"AA" "AB" 1
please suggest query in sql which i can run to do this also i would
like to know is it possible to have some kind of loop which can check
each nametype with other like the combination above please suggest.
Regards

how to compare value of two fileds and based on that insert value into third fileds

Hi,

I have a database with table name as test in that i have 6 colums
they are

name varchar (20)
address varchar (20)
position varchar (20)
nametype1 varchar (20)
nametype2 varchar (20)
nameval varchar(20)

now in the nametype1 and nametype2 there are values like
nametype1 nametype2
"AB" "BA"
"BB" "BB"
"AA" "AA"
"BA" "AB"

now depending upon the combination i want to assign value to the thrid
field that is nameval like example below

nametype1 nametype2 nameval
"AB" "BA" 1
"BB" "BB" 2
"AA" "AA" 2
"BA" "AB" 1

please suggest query in sql which i can run to do this .

RegardsHi,

You do not specify what is the logic for generating the values for the
nameval column, but based on your example seems it is 2 when the values are
equal and 1 when they are different.

Based on that assumption, here is a query that will do the update:

update test
set nameval = (case when nametype1 = nametype2 then 2 else 1 end)

Regards,

Plamen Ratchev
http://www.SQLStudio.com|||On Jan 30, 6:47 pm, "Plamen Ratchev" <Pla...@.SQLStudio.comwrote:

Quote:

Originally Posted by

Hi,
>
You do not specify what is the logic for generating the values for the
nameval column, but based on your example seems it is 2 when the values are
equal and 1 when they are different.
>
Based on that assumption, here is a query that will do the update:
>
update test
set nameval = (case when nametype1 = nametype2 then 2 else 1 end)
>
Regards,
>
Plamen Ratchevhttp://www.SQLStudio.com

hi i have an update to the query , please suggest if the table
structure and results are below then what should i run for no of
fileds

name varchar (20)
address varchar (20)
position varchar (20)
nametype1 varchar (20)
nametype2 varchar (20)
nametype3 varchar(20)
nametype4 varchar(20)
nameval varchar(20)
nameval1 varchar(20)
nameval2 varchar(20)
nameval3 varchar(20)

now in the nametype1 and nametype2 there are values like
nametype1 nametype2 nametype3 nametype4
"AB" "BA" "BB" "BB"
"AA" "AA" "BA" "AB"
"AB" "BA" "BB" "BB"
"AA" "AA" "BA" "AB"

now depending upon the combination i want to assign value to the thrid
field that is nameval like example below

nametype1 nametype2 nameval
"AB" "BA" 1
"AA" "AA" 2
"AB" "BA" 1
"AA" "AA" 2

nametype1 nametype3 nameval1
"AB" "BB" 1
"AA" "BA" 1
"AB" "BB" 1
"AA" "BA" 1

nametype1 nametype4 nameval2
"AB" "BB" 1
"AA" "AB" 1
"AB" "BB" 1
"AA" "AB" 1

please suggest query in sql which i can run to do this also i would
like to know is it possible to have some kind of loop which can check
each nametype with other like the combination above please suggest.

Regards|||I am still confused about the logic and the purpose of this, but since it
seems to follow the same pattern (when equal then 2 else 1), here it is (you
just keep repeating the same for the other "nameval" columns):

update test
set nameval = (case when nametype1 = nametype2 then 2 else 1 end),
nameval1 = (case when nametype1 = nametype3 then 2 else 1 end),
nameval2 = (case when nametype1 = nametype4 then 2 else 1 end)

Also, you can create those "nameval" columns as computed columns and then
you do not have to run the update statements. Something like this:

create table test(
name varchar (20),
address varchar (20),
position varchar (20),
nametype1 varchar (20),
nametype2 varchar (20),
nametype3 varchar(20),
nametype4 varchar(20),
nameval as (case when nametype1 = nametype2 then 2 else 1 end),
nameval1 as (case when nametype1 = nametype3 then 2 else 1 end),
nameval2 as (case when nametype1 = nametype4 then 2 else 1 end))

insert into test (nametype1, nametype2, nametype3, nametype4) values ('AA',
'AA', 'AB', 'BA')

select * from test

drop table test

Perhaps the table should be normalized too, but since no requirements are
given I do not want to guess...

HTH,

Plamen Ratchev
http://www.SQLStudio.com|||On Jan 31, 11:42 am, "Plamen Ratchev" <Pla...@.SQLStudio.comwrote:

Quote:

Originally Posted by

I am still confused about the logic and the purpose of this, but since it
seems to follow the same pattern (when equal then 2 else 1), here it is (you
just keep repeating the same for the other "nameval" columns):
>
update test
set nameval = (case when nametype1 = nametype2 then 2 else 1 end),
nameval1 = (case when nametype1 = nametype3 then 2 else 1 end),
nameval2 = (case when nametype1 = nametype4 then 2 else 1 end)
>
Also, you can create those "nameval" columns as computed columns and then
you do not have to run the update statements. Something like this:
>
create table test(
name varchar (20),
address varchar (20),
position varchar (20),
nametype1 varchar (20),
nametype2 varchar (20),
nametype3 varchar(20),
nametype4 varchar(20),
nameval as (case when nametype1 = nametype2 then 2 else 1 end),
nameval1 as (case when nametype1 = nametype3 then 2 else 1 end),
nameval2 as (case when nametype1 = nametype4 then 2 else 1 end))
>
insert into test (nametype1, nametype2, nametype3, nametype4) values ('AA',
'AA', 'AB', 'BA')
>
select * from test
>
drop table test
>
Perhaps the table should be normalized too, but since no requirements are
given I do not want to guess...
>
HTH,
>
Plamen Ratchevhttp://www.SQLStudio.com

is it possible to run the query in existing table , please suggest how
and also if there more than 20 nametypes is there query which can loop
through all the name types and do the job.

please suggest any reference too if any.

Regards|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

Even your narrative is wrong. Did you know that double quotes are not
used for string values in SQL? Have you heard of ISO-11179 rules for
data element names? Absurd things like "name_type_1" look like a
repeating group in violationof 1NF which will lead to some really
horrible kludges and a loss of data integrity.

Please try again and pretend that you have to work from these specs
without any prior knowledge -- we do SQL, not mind-reading :)|||Yes, the query will update an existing table, just keep adding lines for
each column, like this:

update test
set nameval = (case when nametype1 = nametype2 then 2 else 1 end),
nameval1 = (case when nametype1 = nametype3 then 2 else 1 end),
nameval2 = (case when nametype1 = nametype4 then 2 else 1 end),
nameval3 = (case when nametype1 = nametype5 then 2 else 1 end),
...

As for you reference to do the update in a loop, then this is doable via
dynamic SQL. Here is a quick sketch of how it may look (by adjusting the
number 10 you will get different number of columns, beware of the 4000
character limit on the SQL string):

DECLARE @.sql nvarchar(4000),
@.count int,
@.numcolumns int

SELECT @.sql = 'update test set ', @.count = 1, @.numcolumns = 10

WHILE @.count <= @.numcolumns
BEGIN
IF @.count = 1
SELECT @.sql = @.sql + 'nameval = (case when nametype1 = nametype' +
CAST(@.count + 1 as nvarchar) + ' then 2 else 1 end)'
ELSE
SELECT @.sql = @.sql + ', nameval' + CAST(@.count - 1 as nvarchar) + ' =
(case when nametype1 = nametype' + CAST(@.count + 1 as nvarchar) + ' then 2
else 1 end)'

SELECT @.count = @.count + 1
END

EXEC(@.sql)

Erland Sommarskog has an excellent guide on dynamic SQL at
http://www.sommarskog.se/dynamic_sql.html. I would recommend reading it
before jumping into using dynamic SQL. Also, please read the comment from
Celko, he is correct that without posting DLL and specifications it is very
difficult to get good answers.

Regards,

Plamen Ratchev
http://www.SQLStudio.com|||Tradeorganizer wrote:

Quote:

Originally Posted by

I have a database with table name as test in that i have 6 colums
they are
>
name varchar (20)
address varchar (20)
position varchar (20)
nametype1 varchar (20)
nametype2 varchar (20)
nameval varchar(20)
>
now in the nametype1 and nametype2 there are values like
nametype1 nametype2
"AB" "BA"
"BB" "BB"
"AA" "AA"
"BA" "AB"
>
now depending upon the combination i want to assign value to the thrid
field that is nameval like example below
>
nametype1 nametype2 nameval
"AB" "BA" 1
"BB" "BB" 2
"AA" "AA" 2
"BA" "AB" 1
>
please suggest query in sql which i can run to do this .

A couple of things that others have touched on, but not made
quite this explicit:

/Why/ should nametype1 = "AB" and nametype2 = "BA" lead to
nameval = 1? What is the general rule that you want to apply?

Why do you have more than 20 nametypes? Please give serious
consideration to splitting this table into two tables, e.g.

[table1]
person_id, name, address, position
1, 'John Doe', '123 Cherry Lane', 'Regional Manager'
2, 'Thomas Atkins', '987 Easy Street', 'President'

[table2]
person_id, nameindex, nametype, nameval
1, 1, 'AB', 1
1, 2, 'BA', 1
2, 1, 'BB', 2
2, 2, 'BB', 2|||Thanks for great help , yes its working for me.

Thanks to all for taking time to explain me.

Regards

Ed Murphy wrote:

Quote:

Originally Posted by

Tradeorganizer wrote:
>

Quote:

Originally Posted by

I have a database with table name as test in that i have 6 colums
they are

name varchar (20)
address varchar (20)
position varchar (20)
nametype1 varchar (20)
nametype2 varchar (20)
nameval varchar(20)

now in the nametype1 and nametype2 there are values like
nametype1 nametype2
"AB" "BA"
"BB" "BB"
"AA" "AA"
"BA" "AB"

now depending upon the combination i want to assign value to the thrid
field that is nameval like example below

nametype1 nametype2 nameval
"AB" "BA" 1
"BB" "BB" 2
"AA" "AA" 2
"BA" "AB" 1

please suggest query in sql which i can run to do this .

>
A couple of things that others have touched on, but not made
quite this explicit:
>
/Why/ should nametype1 = "AB" and nametype2 = "BA" lead to
nameval = 1? What is the general rule that you want to apply?
>
Why do you have more than 20 nametypes? Please give serious
consideration to splitting this table into two tables, e.g.
>
[table1]
person_id, name, address, position
1, 'John Doe', '123 Cherry Lane', 'Regional Manager'
2, 'Thomas Atkins', '987 Easy Street', 'President'
>
[table2]
person_id, nameindex, nametype, nameval
1, 1, 'AB', 1
1, 2, 'BA', 1
2, 1, 'BB', 2
2, 2, 'BB', 2

how to compare value of two fileds and based on that insert value into third fileds

Hi,
I have a database with table name as test in that i have 6 colums
they are
name varchar (20)
address varchar (20)
position varchar (20)
nametype1 varchar (20)
nametype2 varchar (20)
nameval varchar(20)
now in the nametype1 and nametype2 there are values like
nametype1 nametype2
"AB" "BA"
"BB" "BB"
"AA" "AA"
"BA" "AB"
now depending upon the combination i want to assign value to the thrid
field that is nameval like example below
nametype1 nametype2 nameval
"AB" "BA" 1
"BB" "BB" 2
"AA" "AA" 2
"BA" "AB" 1
please suggest query in sql which i can run to do this .
RegardsYou can use a computed column. Check this script:
CREATE TABLE dbo.t1
(nametype1 char(2),
nametype2 char(2),
nameval AS
CASE
WHEN nametype1 = nametype2 THEN 2
WHEN nametype1 = REVERSE(nametype2) THEN 1
ELSE 0 -- error?
END)
GO
INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('AB','BA')
INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('BB','BB')
INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('AA','AA')
INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('BA','AB')
SELECT *
FROM dbo.t1
Dejan Sarka
http://www.solidqualitylearning.com/blogs/
"Tradeorganizer" <tradeorganizer@.gmail.com> wrote in message
news:1170146292.237194.199580@.q2g2000cwa.googlegroups.com...
> Hi,
> I have a database with table name as test in that i have 6 colums
> they are
> name varchar (20)
> address varchar (20)
> position varchar (20)
> nametype1 varchar (20)
> nametype2 varchar (20)
> nameval varchar(20)
> now in the nametype1 and nametype2 there are values like
> nametype1 nametype2
> "AB" "BA"
> "BB" "BB"
> "AA" "AA"
> "BA" "AB"
> now depending upon the combination i want to assign value to the thrid
> field that is nameval like example below
> nametype1 nametype2 nameval
> "AB" "BA" 1
> "BB" "BB" 2
> "AA" "AA" 2
> "BA" "AB" 1
> please suggest query in sql which i can run to do this .
> Regards
>|||On Jan 30, 3:52 pm, "Dejan Sarka"
<dejan_please_reply_to_newsgroups.sa...@.avtenta.si> wrote:[vbcol=seagreen]
> You can use a computed column. Check this script:
> CREATE TABLE dbo.t1
> (nametype1 char(2),
> nametype2 char(2),
> nameval AS
> CASE
> WHEN nametype1 = nametype2 THEN 2
> WHEN nametype1 = REVERSE(nametype2) THEN 1
> ELSE 0 -- error?
> END)
> GO
> INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('AB','BA')
> INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('BB','BB')
> INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('AA','AA')
> INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('BA','AB')
> SELECT *
> FROM dbo.t1
> --
> Dejan Sarkahttp://www.solidqualitylearning.com/blogs/
> "Tradeorganizer" <tradeorgani...@.gmail.com> wrote in message
> news:1170146292.237194.199580@.q2g2000cwa.googlegroups.com...
>
>
>
>
>
>
>
>
instead of creatating in new table can i update the value in
existing table please suggest.|||UPDATE t1 SET nameval =
CASE WHEN nametype1 = nametype2 THEN 1
WHEN nametype1 = REVERSE(nametype2) THEN 2
END
Regards
Amish Shah
http://shahamishm.tripod.com
On Jan 30, 4:16 pm, "Tradeorganizer" <tradeorgani...@.gmail.com> wrote:
> On Jan 30, 3:52 pm, "Dejan Sarka"
>
>
> <dejan_please_reply_to_newsgroups.sa...@.avtenta.si> wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> instead of creatating in new table can i update the value in
> existing table please suggest.- Hide quoted text -
> - Show quoted text -|||On Jan 30, 5:33 pm, "amish" <shahami...@.gmail.com> wrote:[vbcol=seagreen]
> UPDATE t1 SET nameval =
> CASE WHEN nametype1 = nametype2 THEN 1
> WHEN nametype1 = REVERSE(nametype2) THEN 2
> END
> Regards
> Amish Shahhttp://shahamishm.tripod.com
> On Jan 30, 4:16 pm, "Tradeorganizer" <tradeorgani...@.gmail.com> wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
hi i have an update to the query , please suggest if the table
structure and results are below then what should i run for no of
fileds
name varchar (20)
address varchar (20)
position varchar (20)
nametype1 varchar (20)
nametype2 varchar (20)
nametype3 varchar(20)
nametype4 varchar(20)
nameval varchar(20)
nameval1 varchar(20)
nameval2 varchar(20)
nameval3 varchar(20)
now in the nametype1 and nametype2 there are values like
nametype1 nametype2 nametype3 nametype4
"AB" "BA" "BB" "BB"
"AA" "AA" "BA" "AB"
"AB" "BA" "BB" "BB"
"AA" "AA" "BA" "AB"
now depending upon the combination i want to assign value to the thrid
field that is nameval like example below
nametype1 nametype2 nameval
"AB" "BA" 1
"AA" "AA" 2
"AB" "BA" 1
"AA" "AA" 2
nametype1 nametype3 nameval1
"AB" "BB" 1
"AA" "BA" 1
"AB" "BB" 1
"AA" "BA" 1
nametype1 nametype4 nameval2
"AB" "BB" 1
"AA" "AB" 1
"AB" "BB" 1
"AA" "AB" 1
please suggest query in sql which i can run to do this also i would
like to know is it possible to have some kind of loop which can check
each nametype with other like the combination above please suggest.
Regards

how to compare value of two fileds and based on that insert value into third fileds

Hi,
I have a database with table name as test in that i have 6 colums
they are
name varchar (20)
address varchar (20)
position varchar (20)
nametype1 varchar (20)
nametype2 varchar (20)
nameval varchar(20)
now in the nametype1 and nametype2 there are values like
nametype1 nametype2
"AB" "BA"
"BB" "BB"
"AA" "AA"
"BA" "AB"
now depending upon the combination i want to assign value to the thrid
field that is nameval like example below
nametype1 nametype2 nameval
"AB" "BA" 1
"BB" "BB" 2
"AA" "AA" 2
"BA" "AB" 1
please suggest query in sql which i can run to do this .
RegardsYou can use a computed column. Check this script:
CREATE TABLE dbo.t1
(nametype1 char(2),
nametype2 char(2),
nameval AS
CASE
WHEN nametype1 = nametype2 THEN 2
WHEN nametype1 = REVERSE(nametype2) THEN 1
ELSE 0 -- error?
END)
GO
INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('AB','BA')
INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('BB','BB')
INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('AA','AA')
INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('BA','AB')
SELECT *
FROM dbo.t1
--
Dejan Sarka
http://www.solidqualitylearning.com/blogs/
"Tradeorganizer" <tradeorganizer@.gmail.com> wrote in message
news:1170146292.237194.199580@.q2g2000cwa.googlegroups.com...
> Hi,
> I have a database with table name as test in that i have 6 colums
> they are
> name varchar (20)
> address varchar (20)
> position varchar (20)
> nametype1 varchar (20)
> nametype2 varchar (20)
> nameval varchar(20)
> now in the nametype1 and nametype2 there are values like
> nametype1 nametype2
> "AB" "BA"
> "BB" "BB"
> "AA" "AA"
> "BA" "AB"
> now depending upon the combination i want to assign value to the thrid
> field that is nameval like example below
> nametype1 nametype2 nameval
> "AB" "BA" 1
> "BB" "BB" 2
> "AA" "AA" 2
> "BA" "AB" 1
> please suggest query in sql which i can run to do this .
> Regards
>|||On Jan 30, 3:52 pm, "Dejan Sarka"
<dejan_please_reply_to_newsgroups.sa...@.avtenta.si> wrote:
> You can use a computed column. Check this script:
> CREATE TABLE dbo.t1
> (nametype1 char(2),
> nametype2 char(2),
> nameval AS
> CASE
> WHEN nametype1 = nametype2 THEN 2
> WHEN nametype1 = REVERSE(nametype2) THEN 1
> ELSE 0 -- error?
> END)
> GO
> INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('AB','BA')
> INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('BB','BB')
> INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('AA','AA')
> INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('BA','AB')
> SELECT *
> FROM dbo.t1
> --
> Dejan Sarkahttp://www.solidqualitylearning.com/blogs/
> "Tradeorganizer" <tradeorgani...@.gmail.com> wrote in message
> news:1170146292.237194.199580@.q2g2000cwa.googlegroups.com...
> > Hi,
> > I have a database with table name as test in that i have 6 colums
> > they are
> > name varchar (20)
> > address varchar (20)
> > position varchar (20)
> > nametype1 varchar (20)
> > nametype2 varchar (20)
> > nameval varchar(20)
> > now in the nametype1 and nametype2 there are values like
> > nametype1 nametype2
> > "AB" "BA"
> > "BB" "BB"
> > "AA" "AA"
> > "BA" "AB"
> > now depending upon the combination i want to assign value to the thrid
> > field that is nameval like example below
> > nametype1 nametype2 nameval
> > "AB" "BA" 1
> > "BB" "BB" 2
> > "AA" "AA" 2
> > "BA" "AB" 1
> > please suggest query in sql which i can run to do this .
> > Regards
instead of creatating in new table can i update the value in
existing table please suggest.|||UPDATE t1 SET nameval =CASE WHEN nametype1 = nametype2 THEN 1
WHEN nametype1 = REVERSE(nametype2) THEN 2
END
Regards
Amish Shah
http://shahamishm.tripod.com
On Jan 30, 4:16 pm, "Tradeorganizer" <tradeorgani...@.gmail.com> wrote:
> On Jan 30, 3:52 pm, "Dejan Sarka"
>
>
> <dejan_please_reply_to_newsgroups.sa...@.avtenta.si> wrote:
> > You can use a computed column. Check this script:
> > CREATE TABLE dbo.t1
> > (nametype1 char(2),
> > nametype2 char(2),
> > nameval AS
> > CASE
> > WHEN nametype1 = nametype2 THEN 2
> > WHEN nametype1 = REVERSE(nametype2) THEN 1
> > ELSE 0 -- error?
> > END)
> > GO
> > INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('AB','BA')
> > INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('BB','BB')
> > INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('AA','AA')
> > INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('BA','AB')
> > SELECT *
> > FROM dbo.t1
> > --
> > Dejan Sarkahttp://www.solidqualitylearning.com/blogs/
> > "Tradeorganizer" <tradeorgani...@.gmail.com> wrote in message
> >news:1170146292.237194.199580@.q2g2000cwa.googlegroups.com...
> > > Hi,
> > > I have a database with table name as test in that i have 6 colums
> > > they are
> > > name varchar (20)
> > > address varchar (20)
> > > position varchar (20)
> > > nametype1 varchar (20)
> > > nametype2 varchar (20)
> > > nameval varchar(20)
> > > now in the nametype1 and nametype2 there are values like
> > > nametype1 nametype2
> > > "AB" "BA"
> > > "BB" "BB"
> > > "AA" "AA"
> > > "BA" "AB"
> > > now depending upon the combination i want to assign value to the thrid
> > > field that is nameval like example below
> > > nametype1 nametype2 nameval
> > > "AB" "BA" 1
> > > "BB" "BB" 2
> > > "AA" "AA" 2
> > > "BA" "AB" 1
> > > please suggest query in sql which i can run to do this .
> > > Regards
> instead of creatating in new table can i update the value in
> existing table please suggest.- Hide quoted text -
> - Show quoted text -|||On Jan 30, 5:33 pm, "amish" <shahami...@.gmail.com> wrote:
> UPDATE t1 SET nameval => CASE WHEN nametype1 = nametype2 THEN 1
> WHEN nametype1 = REVERSE(nametype2) THEN 2
> END
> Regards
> Amish Shahhttp://shahamishm.tripod.com
> On Jan 30, 4:16 pm, "Tradeorganizer" <tradeorgani...@.gmail.com> wrote:
> > On Jan 30, 3:52 pm, "Dejan Sarka"
> > <dejan_please_reply_to_newsgroups.sa...@.avtenta.si> wrote:
> > > You can use a computed column. Check this script:
> > > CREATE TABLE dbo.t1
> > > (nametype1 char(2),
> > > nametype2 char(2),
> > > nameval AS
> > > CASE
> > > WHEN nametype1 = nametype2 THEN 2
> > > WHEN nametype1 = REVERSE(nametype2) THEN 1
> > > ELSE 0 -- error?
> > > END)
> > > GO
> > > INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('AB','BA')
> > > INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('BB','BB')
> > > INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('AA','AA')
> > > INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('BA','AB')
> > > SELECT *
> > > FROM dbo.t1
> > > --
> > > Dejan Sarkahttp://www.solidqualitylearning.com/blogs/
> > > "Tradeorganizer" <tradeorgani...@.gmail.com> wrote in message
> > >news:1170146292.237194.199580@.q2g2000cwa.googlegroups.com...
> > > > Hi,
> > > > I have a database with table name as test in that i have 6 colums
> > > > they are
> > > > name varchar (20)
> > > > address varchar (20)
> > > > position varchar (20)
> > > > nametype1 varchar (20)
> > > > nametype2 varchar (20)
> > > > nameval varchar(20)
> > > > now in the nametype1 and nametype2 there are values like
> > > > nametype1 nametype2
> > > > "AB" "BA"
> > > > "BB" "BB"
> > > > "AA" "AA"
> > > > "BA" "AB"
> > > > now depending upon the combination i want to assign value to the thrid
> > > > field that is nameval like example below
> > > > nametype1 nametype2 nameval
> > > > "AB" "BA" 1
> > > > "BB" "BB" 2
> > > > "AA" "AA" 2
> > > > "BA" "AB" 1
> > > > please suggest query in sql which i can run to do this .
> > > > Regards
> > instead of creatating in new table can i update the value in
> > existing table please suggest.- Hide quoted text -
> > - Show quoted text -
hi i have an update to the query , please suggest if the table
structure and results are below then what should i run for no of
fileds
name varchar (20)
address varchar (20)
position varchar (20)
nametype1 varchar (20)
nametype2 varchar (20)
nametype3 varchar(20)
nametype4 varchar(20)
nameval varchar(20)
nameval1 varchar(20)
nameval2 varchar(20)
nameval3 varchar(20)
now in the nametype1 and nametype2 there are values like
nametype1 nametype2 nametype3 nametype4
"AB" "BA" "BB" "BB"
"AA" "AA" "BA" "AB"
"AB" "BA" "BB" "BB"
"AA" "AA" "BA" "AB"
now depending upon the combination i want to assign value to the thrid
field that is nameval like example below
nametype1 nametype2 nameval
"AB" "BA" 1
"AA" "AA" 2
"AB" "BA" 1
"AA" "AA" 2
nametype1 nametype3 nameval1
"AB" "BB" 1
"AA" "BA" 1
"AB" "BB" 1
"AA" "BA" 1
nametype1 nametype4 nameval2
"AB" "BB" 1
"AA" "AB" 1
"AB" "BB" 1
"AA" "AB" 1
please suggest query in sql which i can run to do this also i would
like to know is it possible to have some kind of loop which can check
each nametype with other like the combination above please suggest.
Regards

How to compare vachar which type :20060324225008 with Datetime?

in my SQL 2000

the column importDate contain Date as a vachar , type is 20060324225008 ( 2006 -year , 03-month, 24-day)

I want to compare this column with today's date, how to transform it?

how to return value 20060324 not 20060324225008?

thank you

Grab the column as a string

string rawNumber = "20060324";

DateTime dtTime = Convert.ToDateTime(rawNumber);

Does it work?

|||

how can I get the value of column = 20060324, is was20060324225008 not 20060324

the problem is how to get 20060324 only

thank you

|||

Select LEFT(yourColumn,8) as newValue FROM yourTable

After this, you can use one of datetime functions to compare this date part with today's date part.

how to compare two word documents using full text search?

can we use full text search and mining algorithms to comapre two word or text documents to find out if they are similar
please help.
thaks for reading

You can do this, but it isn't the best solution all the time. Read here first:

http://www.microsoft.com/technet/itshowcase/content/intdocmgmtsql2005.mspx

And here is an example:

http://www.aspcode.net/articles/l_en-US/t_default/ASP.NET/ASP.NET-2.0/How-toin-practice/File-uploadinghowto-part-3_article_311.aspx

Here is more info:

http://www.simple-talk.com/sql/learn-sql-server/sql-server-full-text-search-language-features/

how to compare two word documents using full text search?

can we use full text search and mining algorithms to comapre two word or text documents to find out if they are similar
please help.
thaks for reading

You can do this, but it isn't the best solution all the time. Read here first:

http://www.microsoft.com/technet/itshowcase/content/intdocmgmtsql2005.mspx

And here is an example:

http://www.aspcode.net/articles/l_en-US/t_default/ASP.NET/ASP.NET-2.0/How-toin-practice/File-uploadinghowto-part-3_article_311.aspx

Here is more info:

http://www.simple-talk.com/sql/learn-sql-server/sql-server-full-text-search-language-features/

How to compare two versions of sqlserver?

HI,
I need to do an installer utility in which I have to install sql tables. In
there, I would need to check the version of sql server and add some
constraints if the sql server version is more than 8 and do alternate action
if its not.
Is there any function like version_compare()?
Thanks & Regards,
Celia
msnews wrote:
> HI,
> I need to do an installer utility in which I have to install sql tables. In
> there, I would need to check the version of sql server and add some
> constraints if the sql server version is more than 8 and do alternate action
> if its not.
> Is there any function like version_compare()?
> Thanks & Regards,
> Celia
>
>
There isn't a "out of the box" version compare function as such. I think
you'll have to use @.@.Version or SERVERPROPERTY and then use the result
you get from here to do your evaluation.
You can look up both options in Books On Line for further info and syntax.
Regards
Steen

How to compare two versions of sqlserver?

HI,
I need to do an installer utility in which I have to install sql tables. In
there, I would need to check the version of sql server and add some
constraints if the sql server version is more than 8 and do alternate action
if its not.
Is there any function like version_compare()?
Thanks & Regards,
Celiamsnews wrote:
> HI,
> I need to do an installer utility in which I have to install sql tables. I
n
> there, I would need to check the version of sql server and add some
> constraints if the sql server version is more than 8 and do alternate acti
on
> if its not.
> Is there any function like version_compare()?
> Thanks & Regards,
> Celia
>
>
There isn't a "out of the box" version compare function as such. I think
you'll have to use @.@.Version or SERVERPROPERTY and then use the result
you get from here to do your evaluation.
You can look up both options in Books On Line for further info and syntax.
Regards
Steen

How to compare two versions of sqlserver?

HI,
I need to do an installer utility in which I have to install sql tables. In
there, I would need to check the version of sql server and add some
constraints if the sql server version is more than 8 and do alternate action
if its not.
Is there any function like version_compare()?
Thanks & Regards,
Celiamsnews wrote:
> HI,
> I need to do an installer utility in which I have to install sql tables. In
> there, I would need to check the version of sql server and add some
> constraints if the sql server version is more than 8 and do alternate action
> if its not.
> Is there any function like version_compare()?
> Thanks & Regards,
> Celia
>
>
There isn't a "out of the box" version compare function as such. I think
you'll have to use @.@.Version or SERVERPROPERTY and then use the result
you get from here to do your evaluation.
You can look up both options in Books On Line for further info and syntax.
Regards
Steen

How to compare two tables

Hello, I don't know how to do this and I'm sure its dead simple.
I have two tables that I'll use on a WHERE condition, and I need to know
that the rows in one are exactly the same rows in the other. I don't want
to return results in my query unless there is an exact match between those
two single column tables...
I've been using a two select ... except two solve my problem, but I know
there must be a simpler way, and obviously I'm stuck and unable to think..
An example:
CREATE TABLE #a(c int)
CREATE TABLE #b(c int)
CREATE TABLE #c(c int)
If I have this:
INSERT INTO #a SELECT 1 UNION ALL SELECT 2
INSERT INTO #b SELECT 1 UNION ALL SELECT 2
INSERT INTO #c SELECT 3
Then I want a condition that will make this (which will return a '3')
simpler:
SELECT *
FROM #c
WHERE
NOT EXISTS (
SELECT c
FROM #a
EXCEPT
SELECT c
FROM #b
)
AND NOT EXISTS (
SELECT c
FROM #b
EXCEPT
SELECT c
FROM #a
)
If instead of the above rows I had this:
INSERT INTO #a SELECT 1
INSERT INTO #b SELECT 1 UNION ALL SELECT 2
I shouldn't have a 3 as an answer...
Please tell me there's a better way!!
Regards,
Pablo
--
:whizzy: adj. (alt. `wizzy') [Sun] Describes a {cuspy} program;
one that is feature-rich and well presented.
-- from The on-line Hacker Jargon File V423
Pablo Montilla
www.odyssey.com.uyThe SQL 2005 operator INTERSECT will compare two result sets and only return
rows that are identical in both sets. NOte that BLOB data types cannot be
compared using UNION, INTERSECT, or EXCEPT.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Pablo Montilla" <melkor@.odyssey.com.uy> wrote in message
news:op.tyyy7xoscj6shk@.chimera.odyssey.com.uy...
> Hello, I don't know how to do this and I'm sure its dead simple.
> I have two tables that I'll use on a WHERE condition, and I need to know
> that the rows in one are exactly the same rows in the other. I don't want
> to return results in my query unless there is an exact match between those
> two single column tables...
> I've been using a two select ... except two solve my problem, but I know
> there must be a simpler way, and obviously I'm stuck and unable to think..
> An example:
> CREATE TABLE #a(c int)
> CREATE TABLE #b(c int)
> CREATE TABLE #c(c int)
> If I have this:
> INSERT INTO #a SELECT 1 UNION ALL SELECT 2
> INSERT INTO #b SELECT 1 UNION ALL SELECT 2
> INSERT INTO #c SELECT 3
> Then I want a condition that will make this (which will return a '3')
> simpler:
>
> SELECT *
> FROM #c
> WHERE
> NOT EXISTS (
> SELECT c
> FROM #a
> EXCEPT
> SELECT c
> FROM #b
> )
> AND NOT EXISTS (
> SELECT c
> FROM #b
> EXCEPT
> SELECT c
> FROM #a
> )
> If instead of the above rows I had this:
> INSERT INTO #a SELECT 1
> INSERT INTO #b SELECT 1 UNION ALL SELECT 2
> I shouldn't have a 3 as an answer...
> Please tell me there's a better way!!
> Regards,
> Pablo
> --
>
> :whizzy: adj. (alt. `wizzy') [Sun] Describes a {cuspy} program;
> one that is feature-rich and well presented.
> -- from The on-line Hacker Jargon File V423
> Pablo Montilla
> www.odyssey.com.uy|||On Thu, 20 Sep 2007 17:36:38 -0300, Geoff N. Hiten
<SQLCraftsman@.gmail.com> wrote:
> The SQL 2005 operator INTERSECT will compare two result sets and only
> return rows that are identical in both sets. NOte that BLOB data types
> cannot be compared using UNION, INTERSECT, or EXCEPT.
>
OK, but I need to know if the two sets of rows are identical. If I
interesct, how do I know if the intersection its formed by all the rows in
both tables?
Thanks,
Pablo
:whizzy: adj. (alt. `wizzy') [Sun] Describes a {cuspy} program;
one that is feature-rich and well presented.
-- from The on-line Hacker Jargon File V423
Pablo Montilla
www.odyssey.com.uy|||OK, use EXCEPT. Think of the three operators this war:
UNION is logical OR
INTERSECT is logical AND
EXCEPT is logical NOT
Applied to sets, not variables.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Pablo Montilla" <melkor@.odyssey.com.uy> wrote in message
news:op.tyy0okgvcj6shk@.chimera.odyssey.com.uy...
> On Thu, 20 Sep 2007 17:36:38 -0300, Geoff N. Hiten
> <SQLCraftsman@.gmail.com> wrote:
>> The SQL 2005 operator INTERSECT will compare two result sets and only
>> return rows that are identical in both sets. NOte that BLOB data types
>> cannot be compared using UNION, INTERSECT, or EXCEPT.
> OK, but I need to know if the two sets of rows are identical. If I
> interesct, how do I know if the intersection its formed by all the rows in
> both tables?
> Thanks,
> Pablo
>
> --
>
> :whizzy: adj. (alt. `wizzy') [Sun] Describes a {cuspy} program;
> one that is feature-rich and well presented.
> -- from The on-line Hacker Jargon File V423
> Pablo Montilla
> www.odyssey.com.uy|||Many thanks for your response. So there is no easier, simpler way than to
do two except checks, to see if any element is on one table but not in the
other?
Pity...;o)
Regards,
Pablo
His mind is so open that the wind whistles through it.
-- Heywood Braun
Pablo Montilla
www.odyssey.com.uy

How to compare two strings

How can I compare two text strings?
DIFFERENCE is not very precise. Is there another string function in T-SQL?
What do you mean by compare? Presumably not =.
Have a look at patindex and soundex.
"Dave" wrote:

> How can I compare two text strings?
> DIFFERENCE is not very precise. Is there another string function in T-SQL?
>
>

How to compare two strings

How can I compare two text strings?
DIFFERENCE is not very precise. Is there another string function in T-SQL?What do you mean by compare? Presumably not =.
Have a look at patindex and soundex.
"Dave" wrote:

> How can I compare two text strings?
> DIFFERENCE is not very precise. Is there another string function in T-SQL
?
>
>

How to compare two strings

How can I compare two text strings?
DIFFERENCE is not very precise. Is there another string function in T-SQL?What do you mean by compare? Presumably not =.
Have a look at patindex and soundex.
"Dave" wrote:
> How can I compare two text strings?
> DIFFERENCE is not very precise. Is there another string function in T-SQL?
>
>