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

Common SQL errors

Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:548103
Hello,
Can someone recommend a good book that explains the various common design
and query errors that can happen in SQL? Things like fan and chasm traps,
circular references, etc.
Many Thanks.SQL PROGRAMMING STYLE|||Is the whole book in caps, or just the title :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1124756158.408802.294350@.g44g2000cwa.googlegroups.com...
> SQL PROGRAMMING STYLE
>|||Here is a small set
http://toponewithties.blogspot.com/...es.blogspot.com
"Bill George" <WovenPath@.frontiernet.net> wrote in message
news:101FE72.4F78%WovenPath@.frontiernet.net...
> Hello,
> Can someone recommend a good book that explains the various common design
> and query errors that can happen in SQL? Things like fan and chasm traps,
> circular references, etc.
> Many Thanks.
>

commit and rollback problem

Hi,

I still haven't got a decent book on relational databases :-)

My stored procedure insert_wire inserts values into two tables (wire and
cablewire). The wire_ref (primary key) will be the same for both inserts.
However, if for any reason the first insert fails then I would like a
rollback system to take place. I have tried testing for an error (@.@.error
<> 0) after the 1st transaction but I just get a syntax error. Am I going
down the right lines here? Any tips appreciated.

Thanks, Mary.

CREATE procedure insert_wire(in wire_ref VARCHAR(22), in standard
VARCHAR(16), in a_color VARCHAR(16), in material VARCHAR(22),
in metres INTEGER, in amps FLOAT(3), in volts FLOAT(3), in ni SMALLINT, in
some_comment VARCHAR(32))
BEGIN
insert into cablewire
values(wire_ref, standard, a_color, material, metres, some_comment);
insert into wire
values(wire_ref, amps, volts, ni);
commit;
END!Mary Walker (123@.123.com) writes:
> I still haven't got a decent book on relational databases :-)
> My stored procedure insert_wire inserts values into two tables (wire and
> cablewire). The wire_ref (primary key) will be the same for both inserts.
> However, if for any reason the first insert fails then I would like a
> rollback system to take place. I have tried testing for an error (@.@.error
><> 0) after the 1st transaction but I just get a syntax error. Am I going
> down the right lines here? Any tips appreciated.

Probably not. Judging from the syntax in your posts, you are using
some other DB engine than Microsoft SQL Server, which is the RDBMS
this group is about. @.@.error, on the other hand is a feature in
MS SQL Server, that I would expect not appear anywhere else, except
for Sybase.

So I think you should first out what product you are using, and then
a forum for that product.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

command line parameters are invalid

I have a package that let me to import data from a excel book to a Sql server data base. When I try to run this package like a step into a SQL server Job it show me the next error.

"The command line parameters are invalid. The step failed."

the "command line" looks like this

/FILE "C:\Project\Package.dtsx" /CONNECTION ConexionExcel;"Provider=Microsoft.Jet.OLEDB.4.0;Da ta Source=;Extended Properties=""EXCEL 8.0;HDR=YES"";" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI

and in my excel conexion is:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=;Extended Properties="EXCEL 8.0;HDR=YES";

When i run the "Command Line" in the "Command window" the error is more expecific

"EXCEL 8.0;HDR=YES;" is not valid

So I chanded the "Command Line" in order to run it in de command window(look the double quote in the excel properties)

/FILE "C:\Project\Package.dtsx" /CONNECTION ConexionExcel;"Provider=Microsoft.Jet.OLEDB.4.0;Da ta Source=;Extended Properties="EXCEL 8.0;HDR=YES";" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI

then the package RUN, but when i tried to do the same thing in the

sql wizzard, it just dont work and it lost the threat from de package and the errors says this time:

Couldn't find the package

Some one who knows the answer or has any idea to helpme please?

thanks

Use in the Import/Export wizard in SQL.

Adamus

|||According to current Books Online, the double quote is the correct method to escape the quotes inside the parameter. Are you running SP1? I believe they made some changes to the parsing of command lines in SP1, so perhaps that may help.|||

I have a windows 2003 server and the SP1 is only for windows vista, I have installed this version but, i don't know if it has some thing to do with that

|||

I have found the soluction,

I you have seen the command line, the excel connection doesn't have a Data Source because it change in a bucle, I modified connection string parameter in the package in oder to recive a variable with all the connection string, then when i programming my step in the job i don't need to especified the excel connection string.

Thanks Cesar for the aswer