Comparing mutual columns in two tables

krontrex · #1 (**permalink**) 09-08-11, 14:14

There are two tables . The values of document.id make a subset to library.book_id . Both columns are not primary keys

Code:

CREATE TABLE document
( 	id 	varchar not null,
        version varchar,
  	num_pages number(10),
        last_version varchar ,	
); 	
CREATE TABLE library
( 	book_id 	varchar not null,
  	version 	varchar 	,
  	supplier_id  varchar,
        arrival_date varchar 	
);

I need to determine the value for the last_version in the document table. It should be set to 1 if there is no entry in the library table library.book_id=document.id having a higher version then document.version and in other case it should be set to zero if there is an entry in the library table which book_id is equal to document.id having a higher version.
I know to describe it but don't know to write sql command can someone help me?
Thanks

krontrex · #2 (**permalink**) 09-09-11, 04:26

Both sql queries are invalid:

Code:

SELECT id, version from document where exists (SELECT book_id, MAX(version) from library group by book_id WHERE library.book_id=document.id AND document.version=library.version)

I get an error near the second "WHERE"

or

Code:

SELECT id, version from document where (id,version) IN (SELECT book_id, MAX(version) from library group by book_id)

I get an operator error with advice that some typecasting is necessary not clear which one
Can someone help me?

shammat · #3 (**permalink**) 09-09-11, 04:47

Quote:

Originally Posted by krontrex

Both sql queries are invalid:

Code:

SELECT id, version from document where exists (SELECT book_id, MAX(version) from library group by book_id WHERE library.book_id=document.id AND document.version=library.version)

I get an error near the second "WHERE"

The group by goes after the WHERE. See the manual for details on the syntax.

Quote:

Code:

SELECT id, version from document where (id,version) IN (SELECT book_id, MAX(version) from library group by book_id)

I get an operator error with advice that some typecasting is necessary not clear which one
Can someone help me?

Geez. Is it so hard to include the actual error message?
That statement works for me (using your table definitions)

Btw: your CREATE TABLE statements are invalid for Postgres (there is no "number" datatype").

And please try to format your SQL statements so that not everything is on a single line. Makes them much more readable!

Internet Services

Web Development

Digital Marketing

Consumer Tech