Comparing two dimensions

Hi, I'm writing an MDX query in VS2003 (using RS2000) with AS2000 and I want to know how can I just display the mesaures for the members of a dimension that are equal to another dimension. For example, If I display dimension1 and dimension2 I get (not all of the members in both dimensions are equal):

Cat - Cat
Cat - Dog
Cat - Car
Cat - Horse
Dog - Cat
Dog - Dog
Dog - Car
Dog - Horse
Car- Cat
Car- Dog
Car- Car
Car- Horse
Horse - Cat
Horse - Dog
Horse - Car
Horse - Horse

But I really want to get:

Cat - Cat
Dog - Dog
Car - Car
Horse - Horse

I've tryed using FILTER and I can't make it work, need help here. When I try to use INTERSECT I get the following error:

TITLE: Microsoft Visual Studio

Query preparation failed.

ADDITIONAL INFORMATION:

Calculation error - unknown error (urn:schemas-microsoft-com:xml-analysis)

BUTTONS:

OK

Filter is the right way to go. somethign like the following should work

Select Filter( {<dim1.hier1.level1>.Member * <dim2.hier2.level2>.Members}, <dim1.hier1.CurrentMember.Name = dim2.hier2.CurrentMember.Name ) on 0 from <cube>

Note that this is a bit of an unusual request and won't perform well on large dimensions as joins are normally built into the structure of the cube rather than done at run time.

|||It worked, thanks a lot man. I'm starting to learn MDX and it's quite amazing.

comparing tables... again...

I asked a similar question not to long ago, but I've just tried the same sort of query for a similar problem and get an error: "Cannot resolve collation conflict for equal to operation". I've no idea what it means. I've had a search around, but can't see anything that seems to do what I'm after.

All I've got is two tables: 'existing_members' and 'forum_users'. I want to select the forum_users.id (my primary key) where their email address also exists in the existing_members table.

Then, I want to insert the forum_users.id into existing_members.forum_users_id

Does that make sense?no, that doesn't make sense

you want to select forum_users ids where the email already exists in existing_members, and then insert them? won't they be duplicates?

it might help to see your query sql, too

rudy|||Are you trying to update a field in table2 with the primary key field from table1 where the email address exists in both table1 and table2 ? Is the email address unique enough to join between the 2 tables ? Please post your update statement.|||OK, here's what I tried:

UPDATE existing_members SET forum_users_id=forum_users.id WHERE email=forum_users.email

but obviously that's not gonna work. I need to kinda do a join on the tables but I'm not sure how?

Cheers..|||UPDATE existing_members
SET forum_users_id=forum_users.id
FROM existing_members, forum.users
WHERE existing_members.email=forum_users.email|||Aha - I didn't know you could do that :)

Right, now I'm getting a "Cannot resolve collation conflict for equal to operation". This would be because the muppets who sent us this (I think their previous data storage policy involved writing stuff on the back of envelopes) have got duplicate entries in there: the same member with a different member number.

So how can I find these duplicate entries? Assuming that the email address is still the best field for comparing individual members, I need to select all entries where the email address occurs elsewhere in the column.

I've tried this, but it seems to be coming up with some weird results:

SELECT email from existing_members WHERE (SELECT COUNT(email) FROM existing_members)>1|||select * from existing_members
where email in
(select email
from existing_members
group by email
having count(*) >1 )sqlsql

Comparing tables

I have two tables in my db, Actor and Movie. Both of them contain a field with ActorID, and Id like to put together a query that returns the ones that exist only in Actor.ActorID, and not the ones that exist in both Actor.ActorID and Movie.ActorID.

Is this something anybody could help me with?

Thanks..

/AndreasSelect a.*
from actors a
LEFT OUTER JOIN
movies m ON
a.actor_id = m.actor_id
where m.actor_id is NULL;|||Originally posted by r123456
Select a.*
from actors a
LEFT OUTER JOIN
movies m ON
a.actor_id = m.actor_id
where m.actor_id is NULL;

One way of doing this is

select a.actor_id
from actors a
where not exists (
select 1
from movies m
where m.actor_id=a.actor_id)

you can also use

select a.actor_id
from actors a
where a.actor_id not in (
select actor_id
from movies
)

Comparing similarities in 2 tables - sql query

hi, I have been working on this for about 3 hours now and I can't get any
further:
I have 2 tables (one table for january records and 1 table for june records)
I need to find every instance where a drug name and quantity dispensed are
the exact same in both tables
Then I need to loop through (or can it be done in the query?) each instance
and display the RX numbers along with the drug names and quantities from each
table where that happens (rx from the january table will be different from
the rx in the june table)
(drug_name,quantity_dispensed and rx is a field which resides in both tables)
something like this:
drug quantity janrx junerx
-- -- -- --
aspirin 15 43323 8879676
aspirin 15 2322 12313
codeine 10 1222 989898
I probably did not make that clear but I will be glad to re-word it if
anyone is available to help me.
Thank-You
John
John
Don't work on sunday:-)))))))
create table #june
(
[id] int not null primary key,
drug varchar(50) not null,
qt int
)
insert into #june values (1,'aspirin',15)
insert into #june values (2,'codein',20)
insert into #june values (3,'codein',40)
insert into #june values (4,'optalgin',100)
create table #jan
(
[id] int not null primary key,
drug varchar(50) not null,
qt int
)
insert into #jan values (1,'aspirin',15)
insert into #jan values (2,'codein',20)
insert into #jan values (3,'codein',10)
insert into #jan values (4,'junk',100)
select j.drug,j.qt from #june j join #jan
on j.drug=#jan.drug and j.qt=#jan.qt
"John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
news:5E1DF03B-88E7-4C5C-9D76-F162DCDA1013@.microsoft.com...
> hi, I have been working on this for about 3 hours now and I can't get any
> further:
> I have 2 tables (one table for january records and 1 table for june
> records)
> I need to find every instance where a drug name and quantity dispensed are
> the exact same in both tables
> Then I need to loop through (or can it be done in the query?) each
> instance
> and display the RX numbers along with the drug names and quantities from
> each
> table where that happens (rx from the january table will be different from
> the rx in the june table)
> (drug_name,quantity_dispensed and rx is a field which resides in both
> tables)
> something like this:
> drug quantity janrx junerx
> -- -- -- --
> aspirin 15 43323 8879676
> aspirin 15 2322 12313
> codeine 10 1222 989898
> I probably did not make that clear but I will be glad to re-word it if
> anyone is available to help me.
>
> Thank-You
> John
>
|||Thanks for such a quick reply !
I don't understand where those tables are created - they don't show up in
the Enterprise manager and when I run the query after i run those create
table queries - it says: invalid object #jan
invalid object #june
"Uri Dimant" wrote:

> John
> Don't work on sunday:-)))))))
>
> create table #june
> (
> [id] int not null primary key,
> drug varchar(50) not null,
> qt int
> )
> insert into #june values (1,'aspirin',15)
> insert into #june values (2,'codein',20)
> insert into #june values (3,'codein',40)
> insert into #june values (4,'optalgin',100)
>
> create table #jan
> (
> [id] int not null primary key,
> drug varchar(50) not null,
> qt int
> )
> insert into #jan values (1,'aspirin',15)
> insert into #jan values (2,'codein',20)
> insert into #jan values (3,'codein',10)
> insert into #jan values (4,'junk',100)
>
> --
> select j.drug,j.qt from #june j join #jan
> on j.drug=#jan.drug and j.qt=#jan.qt
>
>
> "John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
> news:5E1DF03B-88E7-4C5C-9D76-F162DCDA1013@.microsoft.com...
>
>
|||John
Those tables are temporary tables which are created in tempdb database. I
did it just for testing.
The matter is does it work or it doesn't (i meant the solution)?
"John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
news:E67FB1FB-5092-4CA2-B3C3-C59C080910EB@.microsoft.com...[vbcol=seagreen]
> Thanks for such a quick reply !
> I don't understand where those tables are created - they don't show up in
> the Enterprise manager and when I run the query after i run those create
> table queries - it says: invalid object #jan
> invalid object #june
> "Uri Dimant" wrote:

Comparing similarities in 2 tables - sql query

hi, I have been working on this for about 3 hours now and I can't get any
further:
I have 2 tables (one table for january records and 1 table for june records)
I need to find every instance where a drug name and quantity dispensed are
the exact same in both tables
Then I need to loop through (or can it be done in the query?) each instance
and display the RX numbers along with the drug names and quantities from each
table where that happens (rx from the january table will be different from
the rx in the june table)
(drug_name,quantity_dispensed and rx is a field which resides in both tables)
something like this:
drug quantity janrx junerx
-- -- -- --
aspirin 15 43323 8879676
aspirin 15 2322 12313
codeine 10 1222 989898
I probably did not make that clear but I will be glad to re-word it if
anyone is available to help me.
Thank-You
JohnJohn
Don't work on sunday:-)))))))
create table #june
(
[id] int not null primary key,
drug varchar(50) not null,
qt int
)
insert into #june values (1,'aspirin',15)
insert into #june values (2,'codein',20)
insert into #june values (3,'codein',40)
insert into #june values (4,'optalgin',100)
create table #jan
(
[id] int not null primary key,
drug varchar(50) not null,
qt int
)
insert into #jan values (1,'aspirin',15)
insert into #jan values (2,'codein',20)
insert into #jan values (3,'codein',10)
insert into #jan values (4,'junk',100)
select j.drug,j.qt from #june j join #jan
on j.drug=#jan.drug and j.qt=#jan.qt
"John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
news:5E1DF03B-88E7-4C5C-9D76-F162DCDA1013@.microsoft.com...
> hi, I have been working on this for about 3 hours now and I can't get any
> further:
> I have 2 tables (one table for january records and 1 table for june
> records)
> I need to find every instance where a drug name and quantity dispensed are
> the exact same in both tables
> Then I need to loop through (or can it be done in the query?) each
> instance
> and display the RX numbers along with the drug names and quantities from
> each
> table where that happens (rx from the january table will be different from
> the rx in the june table)
> (drug_name,quantity_dispensed and rx is a field which resides in both
> tables)
> something like this:
> drug quantity janrx junerx
> -- -- -- --
> aspirin 15 43323 8879676
> aspirin 15 2322 12313
> codeine 10 1222 989898
> I probably did not make that clear but I will be glad to re-word it if
> anyone is available to help me.
>
> Thank-You
> John
>|||Thanks for such a quick reply !
I don't understand where those tables are created - they don't show up in
the Enterprise manager and when I run the query after i run those create
table queries - it says: invalid object #jan
invalid object #june
"Uri Dimant" wrote:
> John
> Don't work on sunday:-)))))))
>
> create table #june
> (
> [id] int not null primary key,
> drug varchar(50) not null,
> qt int
> )
> insert into #june values (1,'aspirin',15)
> insert into #june values (2,'codein',20)
> insert into #june values (3,'codein',40)
> insert into #june values (4,'optalgin',100)
>
> create table #jan
> (
> [id] int not null primary key,
> drug varchar(50) not null,
> qt int
> )
> insert into #jan values (1,'aspirin',15)
> insert into #jan values (2,'codein',20)
> insert into #jan values (3,'codein',10)
> insert into #jan values (4,'junk',100)
>
> --
> select j.drug,j.qt from #june j join #jan
> on j.drug=#jan.drug and j.qt=#jan.qt
>
>
> "John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
> news:5E1DF03B-88E7-4C5C-9D76-F162DCDA1013@.microsoft.com...
> > hi, I have been working on this for about 3 hours now and I can't get any
> > further:
> >
> > I have 2 tables (one table for january records and 1 table for june
> > records)
> >
> > I need to find every instance where a drug name and quantity dispensed are
> > the exact same in both tables
> >
> > Then I need to loop through (or can it be done in the query?) each
> > instance
> > and display the RX numbers along with the drug names and quantities from
> > each
> > table where that happens (rx from the january table will be different from
> > the rx in the june table)
> >
> > (drug_name,quantity_dispensed and rx is a field which resides in both
> > tables)
> >
> > something like this:
> >
> > drug quantity janrx junerx
> > -- -- -- --
> > aspirin 15 43323 8879676
> > aspirin 15 2322 12313
> > codeine 10 1222 989898
> >
> > I probably did not make that clear but I will be glad to re-word it if
> > anyone is available to help me.
> >
> >
> > Thank-You
> >
> > John
> >
>
>|||John
Those tables are temporary tables which are created in tempdb database. I
did it just for testing.
The matter is does it work or it doesn't (i meant the solution)?
"John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
news:E67FB1FB-5092-4CA2-B3C3-C59C080910EB@.microsoft.com...
> Thanks for such a quick reply !
> I don't understand where those tables are created - they don't show up in
> the Enterprise manager and when I run the query after i run those create
> table queries - it says: invalid object #jan
> invalid object #june
> "Uri Dimant" wrote:
>> John
>> Don't work on sunday:-)))))))
>>
>> create table #june
>> (
>> [id] int not null primary key,
>> drug varchar(50) not null,
>> qt int
>> )
>> insert into #june values (1,'aspirin',15)
>> insert into #june values (2,'codein',20)
>> insert into #june values (3,'codein',40)
>> insert into #june values (4,'optalgin',100)
>>
>> create table #jan
>> (
>> [id] int not null primary key,
>> drug varchar(50) not null,
>> qt int
>> )
>> insert into #jan values (1,'aspirin',15)
>> insert into #jan values (2,'codein',20)
>> insert into #jan values (3,'codein',10)
>> insert into #jan values (4,'junk',100)
>>
>> --
>> select j.drug,j.qt from #june j join #jan
>> on j.drug=#jan.drug and j.qt=#jan.qt
>>
>>
>> "John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
>> news:5E1DF03B-88E7-4C5C-9D76-F162DCDA1013@.microsoft.com...
>> > hi, I have been working on this for about 3 hours now and I can't get
>> > any
>> > further:
>> >
>> > I have 2 tables (one table for january records and 1 table for june
>> > records)
>> >
>> > I need to find every instance where a drug name and quantity dispensed
>> > are
>> > the exact same in both tables
>> >
>> > Then I need to loop through (or can it be done in the query?) each
>> > instance
>> > and display the RX numbers along with the drug names and quantities
>> > from
>> > each
>> > table where that happens (rx from the january table will be different
>> > from
>> > the rx in the june table)
>> >
>> > (drug_name,quantity_dispensed and rx is a field which resides in both
>> > tables)
>> >
>> > something like this:
>> >
>> > drug quantity janrx junerx
>> > -- -- -- --
>> > aspirin 15 43323 8879676
>> > aspirin 15 2322 12313
>> > codeine 10 1222 989898
>> >
>> > I probably did not make that clear but I will be glad to re-word it if
>> > anyone is available to help me.
>> >
>> >
>> > Thank-You
>> >
>> > John
>> >
>>sqlsql

Comparing similarities in 2 tables - sql query

hi, I have been working on this for about 3 hours now and I can't get any
further:
I have 2 tables (one table for january records and 1 table for june records)
I need to find every instance where a drug name and quantity dispensed are
the exact same in both tables
Then I need to loop through (or can it be done in the query?) each instance
and display the RX numbers along with the drug names and quantities from eac
h
table where that happens (rx from the january table will be different from
the rx in the june table)
(drug_name,quantity_dispensed and rx is a field which resides in both tables
)
something like this:
drug quantity janrx junerx
-- -- -- --
aspirin 15 43323 8879676
aspirin 15 2322 12313
codeine 10 1222 989898
I probably did not make that clear but I will be glad to re-word it if
anyone is available to help me.
Thank-You
JohnJohn
Don't work on sunday:-)))))))
create table #june
(
[id] int not null primary key,
drug varchar(50) not null,
qt int
)
insert into #june values (1,'aspirin',15)
insert into #june values (2,'codein',20)
insert into #june values (3,'codein',40)
insert into #june values (4,'optalgin',100)
create table #jan
(
[id] int not null primary key,
drug varchar(50) not null,
qt int
)
insert into #jan values (1,'aspirin',15)
insert into #jan values (2,'codein',20)
insert into #jan values (3,'codein',10)
insert into #jan values (4,'junk',100)
select j.drug,j.qt from #june j join #jan
on j.drug=#jan.drug and j.qt=#jan.qt
"John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
news:5E1DF03B-88E7-4C5C-9D76-F162DCDA1013@.microsoft.com...
> hi, I have been working on this for about 3 hours now and I can't get any
> further:
> I have 2 tables (one table for january records and 1 table for june
> records)
> I need to find every instance where a drug name and quantity dispensed are
> the exact same in both tables
> Then I need to loop through (or can it be done in the query?) each
> instance
> and display the RX numbers along with the drug names and quantities from
> each
> table where that happens (rx from the january table will be different from
> the rx in the june table)
> (drug_name,quantity_dispensed and rx is a field which resides in both
> tables)
> something like this:
> drug quantity janrx junerx
> -- -- -- --
> aspirin 15 43323 8879676
> aspirin 15 2322 12313
> codeine 10 1222 989898
> I probably did not make that clear but I will be glad to re-word it if
> anyone is available to help me.
>
> Thank-You
> John
>|||Thanks for such a quick reply !
I don't understand where those tables are created - they don't show up in
the Enterprise manager and when I run the query after i run those create
table queries - it says: invalid object #jan
invalid object #june
"Uri Dimant" wrote:

> John
> Don't work on sunday:-)))))))
>
> create table #june
> (
> [id] int not null primary key,
> drug varchar(50) not null,
> qt int
> )
> insert into #june values (1,'aspirin',15)
> insert into #june values (2,'codein',20)
> insert into #june values (3,'codein',40)
> insert into #june values (4,'optalgin',100)
>
> create table #jan
> (
> [id] int not null primary key,
> drug varchar(50) not null,
> qt int
> )
> insert into #jan values (1,'aspirin',15)
> insert into #jan values (2,'codein',20)
> insert into #jan values (3,'codein',10)
> insert into #jan values (4,'junk',100)
>
> --
> select j.drug,j.qt from #june j join #jan
> on j.drug=#jan.drug and j.qt=#jan.qt
>
>
> "John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
> news:5E1DF03B-88E7-4C5C-9D76-F162DCDA1013@.microsoft.com...
>
>|||John
Those tables are temporary tables which are created in tempdb database. I
did it just for testing.
The matter is does it work or it doesn't (i meant the solution)?
"John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
news:E67FB1FB-5092-4CA2-B3C3-C59C080910EB@.microsoft.com...[vbcol=seagreen]
> Thanks for such a quick reply !
> I don't understand where those tables are created - they don't show up in
> the Enterprise manager and when I run the query after i run those create
> table queries - it says: invalid object #jan
> invalid object #june
> "Uri Dimant" wrote:
>

comparing query results

1. What is the best way to compare efficiency (performance) of two different
queries that return the same result in general?
2. What if one contains user defined function one query and another without
user defined function? The reason I am asking this is that the optimizer's
cost model do not reflect the work done in UDF in the query execution plan.
ThanksJustin
Compare both exection plans first. In my experience ( sure it depends) that
queries which don't no UDF/s perform better
"Justin" <nospam@.nospam.com> wrote in message
news:O%23$Nz46jGHA.4828@.TK2MSFTNGP04.phx.gbl...
> 1. What is the best way to compare efficiency (performance) of two
> different queries that return the same result in general?
> 2. What if one contains user defined function one query and another
> without user defined function? The reason I am asking this is that the
> optimizer's cost model do not reflect the work done in UDF in the query
> execution plan.
> Thanks
>|||I'd like to use Profiler to see reads etc. Profiler *does* include the work
done inside an UDF
(something that execution plan or STATISTICS IO doesn't do).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Justin" <nospam@.nospam.com> wrote in message news:O%23$Nz46jGHA.4828@.TK2MSFTNGP04.phx.gbl.
.
> 1. What is the best way to compare efficiency (performance) of two differe
nt queries that return
> the same result in general?
> 2. What if one contains user defined function one query and another withou
t user defined function?
> The reason I am asking this is that the optimizer's cost model do not refl
ect the work done in UDF
> in the query execution plan.
> Thanks
>|||Justin a crit :
> 1. What is the best way to compare efficiency (performance) of two differe
nt
> queries that return the same result in general?
> 2. What if one contains user defined function one query and another withou
t
> user defined function? The reason I am asking this is that the optimizer'
s
> cost model do not reflect the work done in UDF in the query execution plan
.
> Thanks
>
The most effective parameters in order are :
1) IO, so execute your queries with this flag set :
SET STATISTICS IO ON /OFF
2) the time taken by CPU and other subsystems, so
SET STATISTICS TIME ON
The exection plan is not a good indicator because it is not quiclky
readable and the number of basic steps is not correlated with time or IO
consummation.
A +
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************

Comparing fields within a select

Hi there,

Is it possible for a query to return all the records in a table which have the same value in a given column?

SELECT * FROM table1 WHERE field1 = 2

..would return all the records from table1 which have a field1 value of 2 but I don't want to specify the value - just that all the records have the same value.

Cheers,

WT.I'm not sure I understand what you're trying to do. Are you trying to return all records where the values in field1 are repeated? Can you provide an example to clarify?|||I think I understand what you are asking for. You're looking for all records where there are duplicates in field1.
Kind of the opposite of a DISTINCT query.
If you do a self join you can get what you want.
Here's the generic form...
SELECT * FROM table1 WHERE field1 IN (SELECT DISTINCT T1.field1 FROM table1.T1, table1.T2 WHERE T1.field1=T2.field1 AND T1.Field2<>T2.field2)

Here's an example using the Customers table from NorthWind that will return all the customers in cities where there is more than one customer in that city...
SELECT DISTINCT C1.CITY FROM Customers C1, Customers C2 WHERE C1.City = C2.City and C1.CustomerID <> C2.CustomerID|||An easier way to do this (if I understand your question):

SELECT CompanyID, COUNT(*)
Companies
GROUP BY CompanyID
HAVING COUNT(*) > 1

Comparing datetime fields results in slow query performance

Hi,
I am currently deduping a data warehouse containing around 3 million
records. Most of my dedupe scripts run in 3 or 4 minutes but the
scripts which compare datetime fields take up to 3 hours. I have a
non-clustered index on the date column. Can anybody offer any advice on
how I might improve query times please?
Thanks,
Charlie.which version of SQL Server? (2000 or 2005)
what the index plan says?
what type of comparison do you do?
what the index tunning wizard says against your query?
comparing date/time is slower then comparing integer.
<chairleg@.gmail.com> wrote in message
news:1137577249.061546.244550@.g43g2000cwa.googlegroups.com...
> Hi,
> I am currently deduping a data warehouse containing around 3 million
> records. Most of my dedupe scripts run in 3 or 4 minutes but the
> scripts which compare datetime fields take up to 3 hours. I have a
> non-clustered index on the date column. Can anybody offer any advice on
> how I might improve query times please?
> Thanks,
> Charlie.
>|||In addition to the previous post, my guess is you use the columns inside an
expression, so they are not Searchable ARGuments (SARGs) anymore. Try to
rewrite the queries to have the indexed datetime columns without an
expression in the Where clause. For example:
create table a
(a datetime)
create index aa on a(a)
insert into a values ('2006-01-16')
insert into a values ('2006-01-17')
go
select a from a
where datediff(dd,a,getdate()) < 2 -- this query should do an index scan
select a from a
where dateadd(dd,-1,convert(char(10),getdate(),112)) = a -- this query
should do an index seek
However, take care you get correct results - don't forget that you always
have time part in the datetime data. You should check if the Between
operator would be useful for you.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message
<chairleg@.gmail.com> wrote in message
news:1137577249.061546.244550@.g43g2000cwa.googlegroups.com...
> Hi,
> I am currently deduping a data warehouse containing around 3 million
> records. Most of my dedupe scripts run in 3 or 4 minutes but the
> scripts which compare datetime fields take up to 3 hours. I have a
> non-clustered index on the date column. Can anybody offer any advice on
> how I might improve query times please?
> Thanks,
> Charlie.
>

Comparing datetime fields results in slow query performance

Hi,
I am currently deduping a data warehouse containing around 3 million
records. Most of my dedupe scripts run in 3 or 4 minutes but the
scripts which compare datetime fields take up to 3 hours. I have a
non-clustered index on the date column. Can anybody offer any advice on
how I might improve query times please?
Thanks,
Charlie.
which version of SQL Server? (2000 or 2005)
what the index plan says?
what type of comparison do you do?
what the index tunning wizard says against your query?
comparing date/time is slower then comparing integer.
<chairleg@.gmail.com> wrote in message
news:1137577249.061546.244550@.g43g2000cwa.googlegr oups.com...
> Hi,
> I am currently deduping a data warehouse containing around 3 million
> records. Most of my dedupe scripts run in 3 or 4 minutes but the
> scripts which compare datetime fields take up to 3 hours. I have a
> non-clustered index on the date column. Can anybody offer any advice on
> how I might improve query times please?
> Thanks,
> Charlie.
>
|||In addition to the previous post, my guess is you use the columns inside an
expression, so they are not Searchable ARGuments (SARGs) anymore. Try to
rewrite the queries to have the indexed datetime columns without an
expression in the Where clause. For example:
create table a
(a datetime)
create index aa on a(a)
insert into a values ('2006-01-16')
insert into a values ('2006-01-17')
go
select a from a
where datediff(dd,a,getdate()) < 2 -- this query should do an index scan
select a from a
where dateadd(dd,-1,convert(char(10),getdate(),112)) = a -- this query
should do an index seek
However, take care you get correct results - don't forget that you always
have time part in the datetime data. You should check if the Between
operator would be useful for you.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message
<chairleg@.gmail.com> wrote in message
news:1137577249.061546.244550@.g43g2000cwa.googlegr oups.com...
> Hi,
> I am currently deduping a data warehouse containing around 3 million
> records. Most of my dedupe scripts run in 3 or 4 minutes but the
> scripts which compare datetime fields take up to 3 hours. I have a
> non-clustered index on the date column. Can anybody offer any advice on
> how I might improve query times please?
> Thanks,
> Charlie.
>
sqlsql

comparing dates(Minutes, Hours etc)

guys - is this a decent query to pull all columns (dateCreate)
that have a timestamp less than five minutes?
i know its simple, but i've never done a date compare with minutes or hours
in sql server
thanks
rik:o

select top 10 * from ptpuritm
where datediff(MINUTE,dateCreate,getdate()) <=5

select top 10 * from ptpuritm
where datediff(MINUTE,dateCreate,current_timestamp) <=5Type CTRL+K and look at the execution plans for both of the following examples

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99 (dateCreate datetime)
GO

CREATE INDEX myIndex99 ON myTable99(dateCreate)
GO

INSERT INTO myTable99(dateCreate)
SELECT '12/31/1999 23:00:00' UNION ALL
SELECT '12/31/1999 23:10:00' UNION ALL
SELECT '12/31/1999 23:20:00' UNION ALL
SELECT '12/31/1999 23:30:00' UNION ALL
SELECT '12/31/1999 23:40:00' UNION ALL
SELECT '12/31/1999 23:50:00' UNION ALL
SELECT '12/31/1999 23:55:00' UNION ALL
SELECT '12/31/1999 23:56:00' UNION ALL
SELECT '12/31/1999 23:57:00' UNION ALL
SELECT '12/31/1999 23:58:00' UNION ALL
SELECT '12/31/1999 23:59:00' UNION ALL
SELECT '12/31/1999 23:59:59'
GO


SELECT *
FROM myTable99
WHERE datediff(MINUTE,dateCreate,'1/1/2000 00:00:00') <=5

SELECT *
FROM myTable99
WHERE dateCreate <= dateadd(MINUTE,-5,'1/1/2000 00:00:00')

GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO|||Brett, thanks so much for the help on this. Coming from an Oracle background, i can tell you i'm growing to appreciate SQL SERVER each day.
thanks
again|||You like that?

Look here

http://www.sqlteam.com/

Comparing Dates in Subqueries

Hi,

I am trying to write a basic select query with a subquery.

select x
from y
where DateTime > '27 January 2003'
and DateTime < '02 February 2003'
and a = 'ttt'
and x IN (SELECT x
from y
WHERE a = 'vvv'
and rcd.DateTime > '27 January 2003'
and rcd.DateTime < '02 February 2003')

I only want to see the data for the records where ttt happened before vvv.

Any ideas?select x
from y
where DateTime > '27 January 2003'
and DateTime < '02 February 2003'
and a = 'ttt'
and exists
(SELECT *
from y y2
WHERE y2.a = 'vvv'
and y2.DateTime > '27 January 2003'
and y2.DateTime < '02 February 2003'
and y2,DateTime > y.DateTime
)|||select y.x
from y
left join
(
select x,MinDateTime=min(DateTime)
from y
where a='vvv' and DateTime > '27 January 2003' and DateTime < '02 February 2003'
group by x
) sy on sy.x=y.x and a='ttt' and ( (DateTime<MinDateTime) or MinDateTime is null )sqlsql

Comparing Data between Tables

I messed up and now we have two tables that have slightly different
information. What is a query that would allow me to look at the data
differences between the two tables?
Ever Learning Net Admin
On Wed, 16 Nov 2005 11:53:09 -0800, Randaddyz wrote:

>I messed up and now we have two tables that have slightly different
>information. What is a query that would allow me to look at the data
>differences between the two tables?
Hi Randaddyz,
You could use something like below to check for differences:
SELECT a.Column1, a.Column2, a.Column3, ..., a.ColumnN,
b.Column1, b.Column2, b.Column3, ..., b.ColumnN
FROM TableA AS a
FULL OUTER JOIN TableB AS b
ON a.Column1 = b.Column1
AND a.Column2 = b.Column2
AND a.Column3 = b.Column3
......
AND a.ColumnN = b.ColumnN
WHERE a.ColumnX IS NULL
OR b.ColumnX IS NULL
Make sure to include ALL the tables columns in the ON clause.
Make sure that the column chossen for ColumnX does NOT allow NULLs.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Comparing CASE statement products

The query I have written below works fine. However, I now want to uncomment the WHERE clause below to find entries where the PatientAge does not fall between the PAGBeginningAge and PAGEndingAge. However, when I uncomment the WHERE clause line I receive the following error message:

Msg 156, Level 15, State 1, Line 28

Incorrect syntax near the keyword 'FROM'.

SELECT ampfm.rpt_AdtVisit.PatientFullName, ampfm.rpt_AdtVisit.AdmitPriorityCode, ampfm.dct_AdmitPriorityType.AdmitPriorityTypeName,

ampfm.rpt_AdtVisit.AccountNumber, ampfm.rpt_PatientDemographics.PatientAge, ampfm.rpt_PatientDemographics.PatientAgeGroup,

ampfm.rpt_PatientDemographics.PatientSex,

CASE WHEN PatientAgeGroup = '0 - 14' THEN '0' ELSE

CASE WHEN PatientAgeGroup = '15 - 24' THEN '15' ELSE

CASE WHEN PatientAgeGroup = '25 - 34' THEN '25' ELSE

CASE WHEN PatientAgeGroup = '35 - 44' THEN '35' ELSE

CASE WHEN PatientAgeGroup = '45 - 54' THEN '45' ELSE

CASE WHEN PatientAgeGroup = '55 - 64' THEN '55' ELSE

CASE WHEN PatientAgeGroup = '65 - 74' THEN '65' ELSE

CASE WHEN PatientAgeGroup = '75 - 79' THEN '75' ELSE

CASE WHEN PatientAgeGroup = '80 - OVER' THEN '80'

END END END END END END END END END AS PAGBeginningAge,

CASE WHEN PatientAgeGroup = '0 - 14' THEN '14' ELSE

CASE WHEN PatientAgeGroup = '15 - 24' THEN '24' ELSE

CASE WHEN PatientAgeGroup = '25 - 34' THEN '34' ELSE

CASE WHEN PatientAgeGroup = '35 - 44' THEN '44' ELSE

CASE WHEN PatientAgeGroup = '45 - 54' THEN '54' ELSE

CASE WHEN PatientAgeGroup = '55 - 64' THEN '64' ELSE

CASE WHEN PatientAgeGroup = '65 - 74' THEN '74' ELSE

CASE WHEN PatientAgeGroup = '75 - 79' THEN '79' ELSE

CASE WHEN PatientAgeGroup = '80 - OVER' THEN '200'

END END END END END END END END END AS PAGEndingAge

--WHERE PatientAge NOT BETWEEN PAGBeginningAge AND PAGEndingAge

FROM ampfm.dct_AdmitPriorityType INNER JOIN

ampfm.rpt_AdtVisit ON ampfm.dct_AdmitPriorityType.AdmitPriorityTypeCode = ampfm.rpt_AdtVisit.AdmitPriorityCode INNER JOIN

ampfm.rpt_PatientDemographics ON ampfm.rpt_AdtVisit.RegNum = ampfm.rpt_PatientDemographics.RegNum

Two things:

First, your WHERE statment needs to come after the last ON condition.

second, you are not allowed to alias the "column/expression list" of the SELECT clause like this in MS SQL Server as you can do from DB2; you will need to include the entire case statement in your WHERE clause.

You might be able to create an inline TVF to make this complicated CASE statement a little easier to read.

Maybe something like:

Code Snippet

alter function dbo.ageRange
( @.arg_ageGroup varchar(12)
)
returns table
as
return
( select cast(parsename(replace(@.arg_ageGroup,' - ','.'),2)
as int)
as lowPart,
cast( case when parsename(replace(@.arg_ageGroup,' - ','.'),1)
= 'over'
then '200'
else parsename(replace(@.arg_ageGroup,' - ','.'),1)
end as int)
as highPart
)

go

declare @.ageGroup table ( ageGroup varchar(9))
insert into @.ageGroup
select '0 - 14' union all
select '15 - 24' union all
select '25 - 34' union all
select '35 - 44' union all
select '45 - 54' union all
select '55 - 64' union all
select '65 - 74' union all
select '75 - OVER'

select ageGroup,
lowPart,
highPart
from @.ageGroup
cross apply ageRange(ageGroup)

Another idea would be to put your ranges in a table that included (1) the range, (2) the low and (3) the high of the range. Then you could use a CROSS APPLY to fetch the desired numeric LOW and HIGH of the range.

|||

I can't believe I had the WHERE statement before the FROM statement; boy this has been a long day.

I placed the entire case statement in the WHERE clause and everything is working fine now. Thanks!

BTW, I am not familiar with inline TVF. What is it or where could I read up on it?

|||

( I edited my previous response to include it; please take a look. )

|||

Thanks! I may try something like that on my next project.

sqlsql

Comparing a pair

Hi
I would like to have a subquery where I'm comparing 2 values from the
subquery to the main query and unsure on the syntax. I know in Oracle sql
it's something like
WHERE (manager_id , dept_id) IN (SELECT manager_id, dept_id FROM table ...
etc)
Thanks in advance for your help
G
TSQL doesn't have row-value constructors. But EXISTS is often a better way
than using IN anyway:
SELECT *
FROM Foo
WHERE EXISTS
(SELECT *
FROM Bar
WHERE bar.manager_id = foo.manager_id
AND bar.dept_id = foo.dept_id)
David Portas
SQL Server MVP
|||ok, will try. thanks
"David Portas" wrote:

> TSQL doesn't have row-value constructors. But EXISTS is often a better way
> than using IN anyway:
> SELECT *
> FROM Foo
> WHERE EXISTS
> (SELECT *
> FROM Bar
> WHERE bar.manager_id = foo.manager_id
> AND bar.dept_id = foo.dept_id)
> --
> David Portas
> SQL Server MVP
> --
>
>

Comparing a Date Value in SQL against Todays Date

I have a query that accesses a series of events in a gridview and would like to only show those that are in the future. I am looking to compare the date/time column against today's date in my SQL query.

Any ideas would be great.

Thanks
Orbital

you can use the t-sql function: getdate() in your query

sqlsql

Compare/Sync functions and plug-ins

I have two questions.

Q1 : (Note: I had posted a similar query in Data Dude (Visual Studio Team System for DB Professionals) forums and got an answer (nothing conclusive though). Given the context, thought this will also be a good forum to run the post). Onto my question :

Data Dude has Compare and Synchronization functionality for SQL Server. Are there any plans to integrate the same as part of SQL Server Management Studio ? We DBAs work only with SSMS and not Visual Studio. Are there any near term/long terms plans in this direction ?

My original post in Data Dude forums can be found here :
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1016505&SiteID=1

Q2 : I came across this thread which discusses about developing Plug-ins for Management Studio.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=192769&SiteID=1
Any approximate time line by which we can expect this ?

Appreciate your response.

Krish

I believe it will be good if you have upadted the same on http://connect.microsoft.com/ with the requirement, as they are monitored on daily basis and your request might be considered in future releases of Service pack.

sqlsql

Compare values

I have two tables both with first name and last name fields. I want to create
a query that pulls the names out of table ctct that are not in table ctctws.
I tried this query but does not give me the expected reults
select ctct.c_last_name, ctct.c_first_name, ctctws.c_last_namews,
ctctws.c_first_namews
from ctct, ctctws
where c_last_name <> c_last_namews and c_first_name <> c_first_namews
Can anyone suggest away to produce the required result.
I Really appreciate any help
Thanks
select ctct.c_last_name, ctct.c_first_name
FROM ctct
WHERE NOT EXISTS(SELECT NULL FROM ctctws
WHERE c_last_name = c_last_namews and c_first_name = c_first_namews)
Jacco Schalkwijk
SQL Server MVP
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:3A9452B9-7BE2-4552-9F32-EE12630E2240@.microsoft.com...
>I have two tables both with first name and last name fields. I want to
>create
> a query that pulls the names out of table ctct that are not in table
> ctctws.
> I tried this query but does not give me the expected reults
> select ctct.c_last_name, ctct.c_first_name, ctctws.c_last_namews,
> ctctws.c_first_namews
> from ctct, ctctws
> where c_last_name <> c_last_namews and c_first_name <> c_first_namews
> Can anyone suggest away to produce the required result.
> I Really appreciate any help
> Thanks
|||I suggest NOT EXISTS. Below is an untested example in which I removed the
ctctws data from the column list since these refer to non-existing rows:
SELECT
ctct.c_last_name,
ctct.c_first_name
FROM ctct
WHERE NOT EXISTS
(
SELECT *
FROM ctctws
WHERE
ctct.c_last_name = ctctws.c_last_namews AND
ctct.c_first_name = ctctws.c_first_namews
)
You could also use LEFT JOIN, but note that unlatching ctctws rows will
contain NULL values.
SELECT
ctct.c_last_name,
ctct.c_first_name,
ctctws.c_last_namews,
c_first_namews
FROM ctct
LEFT JOIN ctctws ON
ctct.c_last_name = ctctws.c_last_namews AND
ctct.c_first_name = ctctws.c_first_namews
WHERE
ctctws.c_last_namews IS NOT NULL
Both of these examples will consider NULL values as unmatching, even if the
values are NULL in both tables. You can add IS NULL criteria to the queries
if you need to consider NULLs as equal for comparison purposes.
Hope this helps.
Dan Guzman
SQL Server MVP
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:3A9452B9-7BE2-4552-9F32-EE12630E2240@.microsoft.com...
>I have two tables both with first name and last name fields. I want to
>create
> a query that pulls the names out of table ctct that are not in table
> ctctws.
> I tried this query but does not give me the expected reults
> select ctct.c_last_name, ctct.c_first_name, ctctws.c_last_namews,
> ctctws.c_first_namews
> from ctct, ctctws
> where c_last_name <> c_last_namews and c_first_name <> c_first_namews
> Can anyone suggest away to produce the required result.
> I Really appreciate any help
> Thanks
|||Great that works thanks!!!!! So that shows me the ones that are not there how
do i find the ones that are there?
Thanks!!!
"Dan Guzman" wrote:

> I suggest NOT EXISTS. Below is an untested example in which I removed the
> ctctws data from the column list since these refer to non-existing rows:
> SELECT
> ctct.c_last_name,
> ctct.c_first_name
> FROM ctct
> WHERE NOT EXISTS
> (
> SELECT *
> FROM ctctws
> WHERE
> ctct.c_last_name = ctctws.c_last_namews AND
> ctct.c_first_name = ctctws.c_first_namews
> )
> You could also use LEFT JOIN, but note that unlatching ctctws rows will
> contain NULL values.
> SELECT
> ctct.c_last_name,
> ctct.c_first_name,
> ctctws.c_last_namews,
> c_first_namews
> FROM ctct
> LEFT JOIN ctctws ON
> ctct.c_last_name = ctctws.c_last_namews AND
> ctct.c_first_name = ctctws.c_first_namews
> WHERE
> ctctws.c_last_namews IS NOT NULL
> Both of these examples will consider NULL values as unmatching, even if the
> values are NULL in both tables. You can add IS NULL criteria to the queries
> if you need to consider NULLs as equal for comparison purposes.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:3A9452B9-7BE2-4552-9F32-EE12630E2240@.microsoft.com...
>
>
|||lol never mind i just replaced WHERE NOT EXISTS with WHERE EXISTS
"Eric" wrote:
[vbcol=seagreen]
> Great that works thanks!!!!! So that shows me the ones that are not there how
> do i find the ones that are there?
> Thanks!!!
> "Dan Guzman" wrote:

Compare values

I have two tables both with first name and last name fields. I want to creat
e
a query that pulls the names out of table ctct that are not in table ctctws.
I tried this query but does not give me the expected reults
select ctct.c_last_name, ctct.c_first_name, ctctws.c_last_namews,
ctctws.c_first_namews
from ctct, ctctws
where c_last_name <> c_last_namews and c_first_name <> c_first_namews
Can anyone suggest away to produce the required result.
I Really appreciate any help
Thanksselect ctct.c_last_name, ctct.c_first_name
FROM ctct
WHERE NOT EXISTS(SELECT NULL FROM ctctws
WHERE c_last_name = c_last_namews and c_first_name = c_first_namews)
Jacco Schalkwijk
SQL Server MVP
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:3A9452B9-7BE2-4552-9F32-EE12630E2240@.microsoft.com...
>I have two tables both with first name and last name fields. I want to
>create
> a query that pulls the names out of table ctct that are not in table
> ctctws.
> I tried this query but does not give me the expected reults
> select ctct.c_last_name, ctct.c_first_name, ctctws.c_last_namews,
> ctctws.c_first_namews
> from ctct, ctctws
> where c_last_name <> c_last_namews and c_first_name <> c_first_namews
> Can anyone suggest away to produce the required result.
> I Really appreciate any help
> Thanks|||I suggest NOT EXISTS. Below is an untested example in which I removed the
ctctws data from the column list since these refer to non-existing rows:
SELECT
ctct.c_last_name,
ctct.c_first_name
FROM ctct
WHERE NOT EXISTS
(
SELECT *
FROM ctctws
WHERE
ctct.c_last_name = ctctws.c_last_namews AND
ctct.c_first_name = ctctws.c_first_namews
)
You could also use LEFT JOIN, but note that unlatching ctctws rows will
contain NULL values.
SELECT
ctct.c_last_name,
ctct.c_first_name,
ctctws.c_last_namews,
c_first_namews
FROM ctct
LEFT JOIN ctctws ON
ctct.c_last_name = ctctws.c_last_namews AND
ctct.c_first_name = ctctws.c_first_namews
WHERE
ctctws.c_last_namews IS NOT NULL
Both of these examples will consider NULL values as unmatching, even if the
values are NULL in both tables. You can add IS NULL criteria to the queries
if you need to consider NULLs as equal for comparison purposes.
Hope this helps.
Dan Guzman
SQL Server MVP
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:3A9452B9-7BE2-4552-9F32-EE12630E2240@.microsoft.com...
>I have two tables both with first name and last name fields. I want to
>create
> a query that pulls the names out of table ctct that are not in table
> ctctws.
> I tried this query but does not give me the expected reults
> select ctct.c_last_name, ctct.c_first_name, ctctws.c_last_namews,
> ctctws.c_first_namews
> from ctct, ctctws
> where c_last_name <> c_last_namews and c_first_name <> c_first_namews
> Can anyone suggest away to produce the required result.
> I Really appreciate any help
> Thanks|||Great that works thanks!!!!! So that shows me the ones that are not there ho
w
do i find the ones that are there?
Thanks!!!
"Dan Guzman" wrote:

> I suggest NOT EXISTS. Below is an untested example in which I removed the
> ctctws data from the column list since these refer to non-existing rows:
> SELECT
> ctct.c_last_name,
> ctct.c_first_name
> FROM ctct
> WHERE NOT EXISTS
> (
> SELECT *
> FROM ctctws
> WHERE
> ctct.c_last_name = ctctws.c_last_namews AND
> ctct.c_first_name = ctctws.c_first_namews
> )
> You could also use LEFT JOIN, but note that unlatching ctctws rows will
> contain NULL values.
> SELECT
> ctct.c_last_name,
> ctct.c_first_name,
> ctctws.c_last_namews,
> c_first_namews
> FROM ctct
> LEFT JOIN ctctws ON
> ctct.c_last_name = ctctws.c_last_namews AND
> ctct.c_first_name = ctctws.c_first_namews
> WHERE
> ctctws.c_last_namews IS NOT NULL
> Both of these examples will consider NULL values as unmatching, even if th
e
> values are NULL in both tables. You can add IS NULL criteria to the queri
es
> if you need to consider NULLs as equal for comparison purposes.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:3A9452B9-7BE2-4552-9F32-EE12630E2240@.microsoft.com...
>
>|||lol never mind i just replaced WHERE NOT EXISTS with WHERE EXISTS
"Eric" wrote:
[vbcol=seagreen]
> Great that works thanks!!!!! So that shows me the ones that are not there
how
> do i find the ones that are there?
> Thanks!!!
> "Dan Guzman" wrote:
>

Compare values

I have two tables both with first name and last name fields. I want to create
a query that pulls the names out of table ctct that are not in table ctctws.
I tried this query but does not give me the expected reults
select ctct.c_last_name, ctct.c_first_name, ctctws.c_last_namews,
ctctws.c_first_namews
from ctct, ctctws
where c_last_name <> c_last_namews and c_first_name <> c_first_namews
Can anyone suggest away to produce the required result.
I Really appreciate any help
Thanksselect ctct.c_last_name, ctct.c_first_name
FROM ctct
WHERE NOT EXISTS(SELECT NULL FROM ctctws
WHERE c_last_name = c_last_namews and c_first_name = c_first_namews)
--
Jacco Schalkwijk
SQL Server MVP
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:3A9452B9-7BE2-4552-9F32-EE12630E2240@.microsoft.com...
>I have two tables both with first name and last name fields. I want to
>create
> a query that pulls the names out of table ctct that are not in table
> ctctws.
> I tried this query but does not give me the expected reults
> select ctct.c_last_name, ctct.c_first_name, ctctws.c_last_namews,
> ctctws.c_first_namews
> from ctct, ctctws
> where c_last_name <> c_last_namews and c_first_name <> c_first_namews
> Can anyone suggest away to produce the required result.
> I Really appreciate any help
> Thanks|||I suggest NOT EXISTS. Below is an untested example in which I removed the
ctctws data from the column list since these refer to non-existing rows:
SELECT
ctct.c_last_name,
ctct.c_first_name
FROM ctct
WHERE NOT EXISTS
(
SELECT *
FROM ctctws
WHERE
ctct.c_last_name = ctctws.c_last_namews AND
ctct.c_first_name = ctctws.c_first_namews
)
You could also use LEFT JOIN, but note that unlatching ctctws rows will
contain NULL values.
SELECT
ctct.c_last_name,
ctct.c_first_name,
ctctws.c_last_namews,
c_first_namews
FROM ctct
LEFT JOIN ctctws ON
ctct.c_last_name = ctctws.c_last_namews AND
ctct.c_first_name = ctctws.c_first_namews
WHERE
ctctws.c_last_namews IS NOT NULL
Both of these examples will consider NULL values as unmatching, even if the
values are NULL in both tables. You can add IS NULL criteria to the queries
if you need to consider NULLs as equal for comparison purposes.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:3A9452B9-7BE2-4552-9F32-EE12630E2240@.microsoft.com...
>I have two tables both with first name and last name fields. I want to
>create
> a query that pulls the names out of table ctct that are not in table
> ctctws.
> I tried this query but does not give me the expected reults
> select ctct.c_last_name, ctct.c_first_name, ctctws.c_last_namews,
> ctctws.c_first_namews
> from ctct, ctctws
> where c_last_name <> c_last_namews and c_first_name <> c_first_namews
> Can anyone suggest away to produce the required result.
> I Really appreciate any help
> Thanks|||Great that works thanks!!!!! So that shows me the ones that are not there how
do i find the ones that are there?
Thanks!!!
"Dan Guzman" wrote:
> I suggest NOT EXISTS. Below is an untested example in which I removed the
> ctctws data from the column list since these refer to non-existing rows:
> SELECT
> ctct.c_last_name,
> ctct.c_first_name
> FROM ctct
> WHERE NOT EXISTS
> (
> SELECT *
> FROM ctctws
> WHERE
> ctct.c_last_name = ctctws.c_last_namews AND
> ctct.c_first_name = ctctws.c_first_namews
> )
> You could also use LEFT JOIN, but note that unlatching ctctws rows will
> contain NULL values.
> SELECT
> ctct.c_last_name,
> ctct.c_first_name,
> ctctws.c_last_namews,
> c_first_namews
> FROM ctct
> LEFT JOIN ctctws ON
> ctct.c_last_name = ctctws.c_last_namews AND
> ctct.c_first_name = ctctws.c_first_namews
> WHERE
> ctctws.c_last_namews IS NOT NULL
> Both of these examples will consider NULL values as unmatching, even if the
> values are NULL in both tables. You can add IS NULL criteria to the queries
> if you need to consider NULLs as equal for comparison purposes.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:3A9452B9-7BE2-4552-9F32-EE12630E2240@.microsoft.com...
> >I have two tables both with first name and last name fields. I want to
> >create
> > a query that pulls the names out of table ctct that are not in table
> > ctctws.
> > I tried this query but does not give me the expected reults
> >
> > select ctct.c_last_name, ctct.c_first_name, ctctws.c_last_namews,
> > ctctws.c_first_namews
> > from ctct, ctctws
> > where c_last_name <> c_last_namews and c_first_name <> c_first_namews
> >
> > Can anyone suggest away to produce the required result.
> > I Really appreciate any help
> >
> > Thanks
>
>|||lol never mind i just replaced WHERE NOT EXISTS with WHERE EXISTS
"Eric" wrote:
> Great that works thanks!!!!! So that shows me the ones that are not there how
> do i find the ones that are there?
> Thanks!!!
> "Dan Guzman" wrote:
> > I suggest NOT EXISTS. Below is an untested example in which I removed the
> > ctctws data from the column list since these refer to non-existing rows:
> >
> > SELECT
> > ctct.c_last_name,
> > ctct.c_first_name
> > FROM ctct
> > WHERE NOT EXISTS
> > (
> > SELECT *
> > FROM ctctws
> > WHERE
> > ctct.c_last_name = ctctws.c_last_namews AND
> > ctct.c_first_name = ctctws.c_first_namews
> > )
> >
> > You could also use LEFT JOIN, but note that unlatching ctctws rows will
> > contain NULL values.
> >
> > SELECT
> > ctct.c_last_name,
> > ctct.c_first_name,
> > ctctws.c_last_namews,
> > c_first_namews
> > FROM ctct
> > LEFT JOIN ctctws ON
> > ctct.c_last_name = ctctws.c_last_namews AND
> > ctct.c_first_name = ctctws.c_first_namews
> > WHERE
> > ctctws.c_last_namews IS NOT NULL
> >
> > Both of these examples will consider NULL values as unmatching, even if the
> > values are NULL in both tables. You can add IS NULL criteria to the queries
> > if you need to consider NULLs as equal for comparison purposes.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Eric" <Eric@.discussions.microsoft.com> wrote in message
> > news:3A9452B9-7BE2-4552-9F32-EE12630E2240@.microsoft.com...
> > >I have two tables both with first name and last name fields. I want to
> > >create
> > > a query that pulls the names out of table ctct that are not in table
> > > ctctws.
> > > I tried this query but does not give me the expected reults
> > >
> > > select ctct.c_last_name, ctct.c_first_name, ctctws.c_last_namews,
> > > ctctws.c_first_namews
> > > from ctct, ctctws
> > > where c_last_name <> c_last_namews and c_first_name <> c_first_namews
> > >
> > > Can anyone suggest away to produce the required result.
> > > I Really appreciate any help
> > >
> > > Thanks
> >
> >
> >sqlsql