Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2010
    Posts
    40

    Unanswered: Comparing mutual columns in two tables

    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
    Last edited by krontrex; 09-08-11 at 15:20.

  2. #2
    Join Date
    Sep 2010
    Posts
    40

    Problem still not resolved!!!!!

    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?

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by krontrex View Post
    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.

    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •