Comparing data in two tables to find missing records

I have two tables of book information. One that has descriptions of the
book in it, and the isbn, and the other that has the book title,
inventory data, prices, the isbn.

Because of some techncal constraints I won't get into now, I can't
combine them both into one table. No problem. Things are going fine as
long as there is a description in the one table to corrispond to the
isbn and other data in the other table.

However, about half of the products are not yet entered into the
descrition table. I'd like to run a sql query that pulls up all the
isbns that don't exist in the other. In other words, I'd like to get a
query that tells me exactly which isbns do not yet have descrition data
in them. I know there is some sql that says to search from one file
where the number does not exist in the other, but it slips my mind. Can
someone help me on this please?

Thank you!

Bill

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Bill,

You can use one of these queries:

select *
from Titles
where not exists(select * from Descriptions where Descriptions.isbn =
Titles.isbn)

OR

select *
from Titles
where isbn not in (select isbn from Descriptions)

OR

select Titles.*
from Titles left outer join Descriptions on Descriptions.isbn =
Titles.isbn
where Descriptions.isbn is null

Shervin

"Bill" <BillZimmerman@.gospellight.com> wrote in message
news:3f8c7948$0$200$75868355@.news.frii.net...
> I have two tables of book information. One that has descriptions of the
> book in it, and the isbn, and the other that has the book title,
> inventory data, prices, the isbn.
> Because of some techncal constraints I won't get into now, I can't
> combine them both into one table. No problem. Things are going fine as
> long as there is a description in the one table to corrispond to the
> isbn and other data in the other table.
> However, about half of the products are not yet entered into the
> descrition table. I'd like to run a sql query that pulls up all the
> isbns that don't exist in the other. In other words, I'd like to get a
> query that tells me exactly which isbns do not yet have descrition data
> in them. I know there is some sql that says to search from one file
> where the number does not exist in the other, but it slips my mind. Can
> someone help me on this please?
> Thank you!
>
> Bill
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Dear Shervin:

Thank you! It worked like a charm.

All the best,

Bill

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!sqlsql

Comparing 2 PCs Inventory

I've decided to try another method to compare 2 PCs inventories via the
ADD Remove Displayname.
I have the following code I'm trying to use to compare the two and put
in the Netbios_Name0 column PTLSTANDARD if it is lacking that software
or the @.name2 computer name if it exceeds the standard. It works fine
when the Displaynames match (the @.name2 computer does not have the
Date/Time data in it) However, when the code finds that the reference
computer and the @.name2 don't match because of the Date/Time data it
displays it as both lacking and exceeding.
I'm racking my brain how to institute some form of LIKE where the
reference computer DISPLAYNAME0% is LIKE @.name2 then toss it out of the
mix.
Here is the code and the output.
Thanks Fred
Declare @.name1 varchar(20)
Declare @.name2 varchar(20)
Set @.name1='PTLSTANDARD' /* THE REFERENCE PC */
Set @.name2='PTLWCSG8F11LAB'
select
(case when inv1.Displayname0 is not null then @.name1 else @.name2 end)
as Netbios_Name0,
(case when inv1.Displayname0 is not null then inv1.Displayname0 else
inv2.Displayname0 end) as DisplayName0
from
(
select s.Displayname0, Version0, Installdate0, ProdId0, Publisher0
from v_GS_ADD_REMOVE_PROGRAMS s
join v_R_System sys on s.resourceID= sys.resourceID
where sys.Netbios_Name0 = @.name1
) as inv1
full outer join
(
select s.Displayname0, Version0, Installdate0, ProdId0, Publisher0
from v_GS_ADD_REMOVE_PROGRAMS s
join v_R_System sys on s.resourceID= sys.resourceID
where sys.Netbios_Name0 = @.name2
) as inv2
on inv1.Displayname0 = inv2.Displayname0
where (inv1.Displayname0 is NULL) or (inv2.Displayname0 is NULL)
Order by Netbios_Name0, Displayname0
Netbios_Name0 Displayname0
========================================
=============================
PTLSTANDARD Catalog for UnumProv_All_03.1
PTLSTANDARD Catalog for UnumProv_All_r054
PTLSTANDARD Client Application Enabler for UDB
PTLSTANDARD COMTI Client Common 2002.7
PTLSTANDARD Flash Player 6
PTLSTANDARD IBM CICS Universal Client
PTLSTANDARD Norton Location Pointer
PTLSTANDARD SNA Pointer
PTLSTANDARD Websphere Client 5.3
PTLSTANDARD XML Parser 3.0 SP2
PTLSTANDARD XML_Parser_4.0_SP1
PTLWCSG8F11LAB Catalog for UnumProv_All_03.1 5/19/2003 2:06:03 PM
PTLWCSG8F11LAB Catalog for UnumProv_All_r054 6/13/2005 11:01:08 AM
PTLWCSG8F11LAB Client Application Enabler for UDB 7.2 4/14/2003 8:23:46
AM
PTLWCSG8F11LAB COM CommAPI 2003.1 6/5/2003 4:19:54 PM
PTLWCSG8F11LAB COMTI Client Common 2002.7 11/14/2003 8:09:28 AM
PTLWCSG8F11LAB Flash Player 6 4/14/2003 8:37:35 AM
PTLWCSG8F11LAB IBM CICS Universal Client 4/14/2003 8:27:06 AM
PTLWCSG8F11LAB Norton Location Pointer 5/17/2005 4:13:00 PM
PTLWCSG8F11LAB SNA Pointer 4/14/2003 8:30:58 AM
PTLWCSG8F11LAB Websphere Client 5.3 4/14/2003 8:42:01 AM
PTLWCSG8F11LAB XML Parser 3.0 SP2 4/12/2003 3:34:58 AM
PTLWCSG8F11LAB XML_Parser_4.0_SP1 4/12/2003 3:47:23 AMHow about if you use a substring, so your comparison would be something like
this:
inv1.Displayname0 = substring(inv2.Displayname0, 1, len(inv1.Displayname0))
You can use the left function for this too which would be:
inv1.Displayname0 = left(inv2.Displayname0, len(inv1.Displayname0))
Sean
"FStuart" <fstuart1@.maine.rr.com> wrote in message
news:1122056014.704562.15960@.g44g2000cwa.googlegroups.com...
> I've decided to try another method to compare 2 PCs inventories via the
> ADD Remove Displayname.
> I have the following code I'm trying to use to compare the two and put
> in the Netbios_Name0 column PTLSTANDARD if it is lacking that software
> or the @.name2 computer name if it exceeds the standard. It works fine
> when the Displaynames match (the @.name2 computer does not have the
> Date/Time data in it) However, when the code finds that the reference
> computer and the @.name2 don't match because of the Date/Time data it
> displays it as both lacking and exceeding.
> I'm racking my brain how to institute some form of LIKE where the
> reference computer DISPLAYNAME0% is LIKE @.name2 then toss it out of the
> mix.
> Here is the code and the output.
> Thanks Fred
>
> Declare @.name1 varchar(20)
> Declare @.name2 varchar(20)
> Set @.name1='PTLSTANDARD' /* THE REFERENCE PC */
> Set @.name2='PTLWCSG8F11LAB'
> select
> (case when inv1.Displayname0 is not null then @.name1 else @.name2 end)
> as Netbios_Name0,
> (case when inv1.Displayname0 is not null then inv1.Displayname0 else
> inv2.Displayname0 end) as DisplayName0
> from
> (
> select s.Displayname0, Version0, Installdate0, ProdId0, Publisher0
> from v_GS_ADD_REMOVE_PROGRAMS s
> join v_R_System sys on s.resourceID= sys.resourceID
> where sys.Netbios_Name0 = @.name1
> ) as inv1
> full outer join
> (
> select s.Displayname0, Version0, Installdate0, ProdId0, Publisher0
> from v_GS_ADD_REMOVE_PROGRAMS s
> join v_R_System sys on s.resourceID= sys.resourceID
> where sys.Netbios_Name0 = @.name2
> ) as inv2
> on inv1.Displayname0 = inv2.Displayname0
> where (inv1.Displayname0 is NULL) or (inv2.Displayname0 is NULL)
> Order by Netbios_Name0, Displayname0
>
> Netbios_Name0 Displayname0
> ========================================
=============================
> PTLSTANDARD Catalog for UnumProv_All_03.1
> PTLSTANDARD Catalog for UnumProv_All_r054
> PTLSTANDARD Client Application Enabler for UDB
> PTLSTANDARD COMTI Client Common 2002.7
> PTLSTANDARD Flash Player 6
> PTLSTANDARD IBM CICS Universal Client
> PTLSTANDARD Norton Location Pointer
> PTLSTANDARD SNA Pointer
> PTLSTANDARD Websphere Client 5.3
> PTLSTANDARD XML Parser 3.0 SP2
> PTLSTANDARD XML_Parser_4.0_SP1
> PTLWCSG8F11LAB Catalog for UnumProv_All_03.1 5/19/2003 2:06:03 PM
> PTLWCSG8F11LAB Catalog for UnumProv_All_r054 6/13/2005 11:01:08 AM
> PTLWCSG8F11LAB Client Application Enabler for UDB 7.2 4/14/2003 8:23:46
> AM
> PTLWCSG8F11LAB COM CommAPI 2003.1 6/5/2003 4:19:54 PM
> PTLWCSG8F11LAB COMTI Client Common 2002.7 11/14/2003 8:09:28 AM
> PTLWCSG8F11LAB Flash Player 6 4/14/2003 8:37:35 AM
> PTLWCSG8F11LAB IBM CICS Universal Client 4/14/2003 8:27:06 AM
> PTLWCSG8F11LAB Norton Location Pointer 5/17/2005 4:13:00 PM
> PTLWCSG8F11LAB SNA Pointer 4/14/2003 8:30:58 AM
> PTLWCSG8F11LAB Websphere Client 5.3 4/14/2003 8:42:01 AM
> PTLWCSG8F11LAB XML Parser 3.0 SP2 4/12/2003 3:34:58 AM
> PTLWCSG8F11LAB XML_Parser_4.0_SP1 4/12/2003 3:47:23 AM
>