3

I have a table "temp"

    author  |  title  |  bibkey  | Data
    -----------------------------------
    John    | JsPaper | John2008 |  65
    Kate    | KsPaper |          |  60
            |         | Data2015 |  80

From this I want to produce two tables, a 'sample_table' and a 'ref_table' like so:

sample_table:

    sample_id|ref_id| data
    --------------------------
        1    |  1   |  65
        2    |  2   |  60
        3    |  3   |  80

ref_table:

    ref_id  | author  |  title  | bibkey 
    --------------------------------------
       1    |  John   | JsPaper | John2008
       2    |  Kate   | KsPaper |         
       3    |         |         | Data2015

I've created both tables

    CREATE TABLE ref_table (                CREATE TABLE sample_table (
        ref_id serial PRIMARY KEY,             sample_id serial PRIMARY KEY,
        author text,                           ref_id integer REFERENCES ref_table(ref_id),
        title text,                            data numeric
        bibkey text                         );
        );

And inserted the unique author,title,bibkey rows into the reference table as above. What I want to do now is do the join for the sample_table to get the ref_id's. For my insert statement i currently have:

    INSERT INTO sample_table (
            ref_id,data
        )
        SELECT ref.ref_id, t.data
        FROM
            temp t
            LEFT JOIN
            ref_table ref ON COALESCE(ref.author,'00000') = COALESCE(t.author,'00000')
            AND COALESCE(ref.title,'00000') = COALESCE(t.title,'00000')
            AND COALESCE(ref.bibkey,'00000') = COALESCE(t.bibkey,'00000');

However i really want to have a conditional statement in the join, rather than all 3 like I have:

  1. IF a bibkey exists for that row, I know it is unique, and join only on that.
  2. If bibkey is NULL, then join on both author and title for the unique pair, and not bibkey.

Is this possible?

4
  • bibkey is NULL this check should be happening from which table ? because both the tables are having bibkey column Commented Dec 6, 2016 at 10:29
  • I want to put the ref_id from the ref_table and corresponding data from the temp table into the sample_table. My code after might be wrong (Im new to this), but the example tables shown above are my goals; with the join happening only on the bibkey if it exists, but if it doesnt, use both author and title as the unique pair. Commented Dec 6, 2016 at 10:39
  • Note: you want to populate two tables. You'll first have to add a PK (surrogate key) to your source table if you don't want to lose the association between sample_table and ref_table Commented Dec 6, 2016 at 11:44
  • Why is a surrogate key required? I havent had any issues with the joins yet when using all 3 columns as above for the join, but if I should be using it I'll try to add it in (I'm jumping ahead of my own understanding too much i think...). Commented Dec 6, 2016 at 14:06

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.