Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Join Date
    Feb 2009
    Posts
    54

    Unanswered: do not print duplicate of left side of join

    I have a query of two related tables that gives this:
    Code:
    column1 column2
    a       1
    a       2
    a       3
    how do I achieve this:
    Code:
    column1 column2
    a       1
            2
            3
    In the manual I'm probably looking in the wrong place and cannot find it

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    don't try to do this with sql -- use your front-end application language
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2009
    Posts
    54
    I see, thanks

  4. #4
    Join Date
    Feb 2009
    Posts
    54
    I just realized I don't have a front end application
    I run postgres on OpenBSD 4.3 without a gui environment.
    Any suggestions? Is it possible to do that with a function? If so, how?
    I would especially need some hints on where to find the answer, I think I've been googleing around using the wrong keywords.
    thanks

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no front-end application?

    so, what, this database has 1 user? and that's you?

    okay, my advice changes slightly then -- just live with the results as presented by the query

    (that was easy, wasn't it)

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2009
    Posts
    54
    it is my very own personal database, and of course I can live with the results of the query as I have done until now.
    I guess I should decide myself to learn some real programming and be able to solve these trivial things by myself.
    Thank you anyway.

  7. #7
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Try this:
    Code:
    SELECT CASE 
              WHEN t1.column1 = t2.col1_min THEN t1.column1
              ELSE null
           END, t1.column2
    FROM the_table t1 
      JOIN (SELECT column2, min(column1) as col1_min
            FROM the table
            GROUP BY column2) t2 ON t1.column2 = t2.column2 
    order by t1.column2;
    With the analytical functions in the upcoming Postgres 8.4 release it will be a bit easier:
    Code:
    SELECT CASE row_number() over (PARTITION BY coumn2 ORDER BY column1)
             WHEN 1 THEN column1
             ELSE NULL
           END AS col1_display,
           column2
    FROM the_table
    ORDER BY column2

  8. #8
    Join Date
    Feb 2009
    Posts
    54
    Thank you shammat! You always save me.
    Only, it does not work exactly as expected, probably because my query has in fact 8 columns, from 8 tables related between each other.
    Where can I find some more information to work it out by myself (the explanation in the manual pages do not help much). Or, if you are so patient to try and explain me...
    Also, I need the results to be ordered by column1, this is a must.

  9. #9
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by shwe
    Only, it does not work exactly as expected, probably because my query has in fact 8 columns, from 8 tables related between each other.
    Where can I find some more information to work it out by myself (the explanation in the manual pages do not help much). Or, if you are so patient to try and explain me...
    Post the DDL for the table(s) and the some sample data.
    Also, I need the results to be ordered by column1, this is a must.
    Hmm, this will be a bit complicated as the only way to get the display, is to "remove" the values, and then sorting is not really possible.

    Maybe if you post your original query I might be able to find something (but sample data is really important here)

  10. #10
    Join Date
    Feb 2009
    Posts
    54
    please excuse the quite cryptic names, they are in fact very meaningfull (at least to me).
    I have this query:
    Code:
    SELECT manci.manci, hanci.hanci, mancizu.mancizu, hancizu.hancizu, mancizu_mancizuda.zuga, manju.manju, hanju.hanju, manju_manjuda.juga
       FROM manci
       LEFT JOIN manci_hanci ON manci.manci_id = manci_hanci.manci_id
       LEFT JOIN hanci ON manci_hanci.hanci_id = hanci.hanci_id
       LEFT JOIN manci_mancizu ON manci.manci_id = manci_mancizu.manci_id
       LEFT JOIN manci_mancida  ON manci.manci_id = manci_mancida.manci_id
       LEFT JOIN mancida ON manci_mancida.mancida_id = mancida.mancida_id
       LEFT JOIN mancizu ON manci_mancizu.mancizu_id = mancizu.mancizu_id
       LEFT JOIN mancizu_mancizuda ON mancizu.mancizu_id  = mancizu_mancizuda.mancizu_id
       LEFT JOIN mancizuda ON mancizu_mancizuda.mancizuda_id  = mancizuda.mancizuda_id
       LEFT JOIN mancizu_hancizu ON mancizu.mancizu_id = mancizu_hancizu.mancizu_id
       LEFT JOIN hancizu ON mancizu_hancizu.hancizu_id = hancizu.hancizu_id
       LEFT JOIN manci_manju ON manci.manci_id = manci_manju.manci_id
       LEFT JOIN manju ON manci_manju.manju_id = manju.manju_id
       LEFT JOIN manju_manjuda ON manju.manju_id = manju_manjuda.manju_id
       LEFT JOIN manjuda ON manju_manjuda.manjuda_id = manjuda.manjuda_id
       LEFT JOIN manju_hanju ON manju.manju_id = manju_hanju.manju_id
       LEFT JOIN hanju ON manju_hanju.hanju_id = hanju.hanju_id
    WHERE mancida.mancida_id = 1 ORDER BY manci;
    that gives me this result:
    Code:
         manci      |            hanci            |         mancizu          |      hancizu       | zuga |                 manju                 |       hanju        | juga
    
     bithe          | 书,书札,文                | alibume bithe            | 呈文(下对上)     |      |                                       |                    | 
     bithe          | 书,书札,文                | benjihe bithe            | 来文               | 4    |                                       |                    | 
     bithe          | 书,书札,文                | unggimbi bithe           | 咨文(平行)       | 22   |                                       |                    | 
     bithe          | 书,书札,文                | bithe unggimbi           | 咨文(平行)       | 113  |                                       |                    |
    As you see, I have in fact two columns from different tables on which I need to do the trick.


    it is all from the following tables:
    Code:
                                    Table "public.manci"
      Column  |         Type          |                    Modifiers                     
    ----------+-----------------------+--------------------------------------------------
     manci_id | integer               | not null default nextval('man_id_seq'::regclass)
     manci    | character varying(50) | 
     geo      | boolean               | 
     guan     | boolean               | 
     ren      | boolean               | 
    Indexes:
        "man_pkey" PRIMARY KEY, btree (manci_id)
        "man_ci_key" UNIQUE, btree (manci)
    Code:
       Table "public.manci_hanci"
      Column  |  Type   | Modifiers 
    ----------+---------+-----------
     manci_id | integer | not null
     hanci_id | integer | not null
    Indexes:
        "manci_hanci_pkey" PRIMARY KEY, btree (manci_id, hanci_id)
    Foreign-key constraints:
        "manci_hanci_hanci_id_fkey" FOREIGN KEY (hanci_id) REFERENCES hanci(hanci_id)
        "manci_hanci_manci_id_fkey" FOREIGN KEY (manci_id) REFERENCES manci(manci_id)
    Code:
                                     Table "public.hanci"
      Column  |         Type          |                     Modifiers                      
    ----------+-----------------------+----------------------------------------------------
     hanci_id | integer               | not null default nextval('hanci_id_seq'::regclass)
     hanci    | character varying(50) | 
     da       | character varying(50) | 
     geo      | boolean               | 
     guan     | boolean               | 
     ren      | boolean               | 
    Indexes:
        "hanci_pkey" PRIMARY KEY, btree (hanci_id)
        "hanci_ju_key" UNIQUE, btree (hanci)
    Code:
       Table "public.manci_mancizu"
       Column   |  Type   | Modifiers 
    ------------+---------+-----------
     manci_id   | integer | not null
     mancizu_id | integer | not null
    Indexes:
        "manci_mancizu_pkey" PRIMARY KEY, btree (manci_id, mancizu_id)
    Foreign-key constraints:
        "manci_mancizu_manci_id_fkey" FOREIGN KEY (manci_id) REFERENCES manci(manci_id)
        "manci_mancizu_mancizu_id_fkey" FOREIGN KEY (mancizu_id) REFERENCES mancizu(mancizu_id)
    Last edited by shwe; 03-16-09 at 12:59.

  11. #11
    Join Date
    Feb 2009
    Posts
    54
    please excuse the quite cryptic names, they are in fact very meaningfull (at least to me).
    I have this query:
    Code:
    SELECT manci.manci, hanci.hanci, mancizu.mancizu, hancizu.hancizu, mancizu_mancizuda.zuga, manju.manju, hanju.hanju, manju_manjuda.juga
       FROM manci
       LEFT JOIN manci_hanci ON manci.manci_id = manci_hanci.manci_id
       LEFT JOIN hanci ON manci_hanci.hanci_id = hanci.hanci_id
       LEFT JOIN manci_mancizu ON manci.manci_id = manci_mancizu.manci_id
       LEFT JOIN manci_mancida  ON manci.manci_id = manci_mancida.manci_id
       LEFT JOIN mancida ON manci_mancida.mancida_id = mancida.mancida_id
       LEFT JOIN mancizu ON manci_mancizu.mancizu_id = mancizu.mancizu_id
       LEFT JOIN mancizu_mancizuda ON mancizu.mancizu_id  = mancizu_mancizuda.mancizu_id
       LEFT JOIN mancizuda ON mancizu_mancizuda.mancizuda_id  = mancizuda.mancizuda_id
       LEFT JOIN mancizu_hancizu ON mancizu.mancizu_id = mancizu_hancizu.mancizu_id
       LEFT JOIN hancizu ON mancizu_hancizu.hancizu_id = hancizu.hancizu_id
       LEFT JOIN manci_manju ON manci.manci_id = manci_manju.manci_id
       LEFT JOIN manju ON manci_manju.manju_id = manju.manju_id
       LEFT JOIN manju_manjuda ON manju.manju_id = manju_manjuda.manju_id
       LEFT JOIN manjuda ON manju_manjuda.manjuda_id = manjuda.manjuda_id
       LEFT JOIN manju_hanju ON manju.manju_id = manju_hanju.manju_id
       LEFT JOIN hanju ON manju_hanju.hanju_id = hanju.hanju_id
    WHERE mancida.mancida_id = 1 ORDER BY manci;
    that gives me this result:
    Code:
         manci      |            hanci            |         mancizu          |      hancizu       | zuga |                 manju                 |       hanju        | juga
    
     bithe          | 书,书札,文                | alibume bithe            | 呈文(下对上)     |      |                                       |                    | 
     bithe          | 书,书札,文                | benjihe bithe            | 来文               | 4    |                                       |                    | 
     bithe          | 书,书札,文                | unggimbi bithe           | 咨文(平行)       | 22   |                                       |                    | 
     bithe          | 书,书札,文                | bithe unggimbi           | 咨文(平行)       | 113  |                                       |                    |
    As you see, I have in fact two columns from different tables on which I need to do the trick.

  12. #12
    Join Date
    Feb 2009
    Posts
    54
    I have to post the ddl separately, otherwise it would never load

    Code:
                                    Table "public.manci"
      Column  |         Type          |                    Modifiers                     
    ----------+-----------------------+--------------------------------------------------
     manci_id | integer               | not null default nextval('man_id_seq'::regclass)
     manci    | character varying(50) | 
     geo      | boolean               | 
     guan     | boolean               | 
     ren      | boolean               | 
    Indexes:
        "man_pkey" PRIMARY KEY, btree (manci_id)
        "man_ci_key" UNIQUE, btree (manci)
    Code:
       Table "public.manci_hanci"
      Column  |  Type   | Modifiers 
    ----------+---------+-----------
     manci_id | integer | not null
     hanci_id | integer | not null
    Indexes:
        "manci_hanci_pkey" PRIMARY KEY, btree (manci_id, hanci_id)
    Foreign-key constraints:
        "manci_hanci_hanci_id_fkey" FOREIGN KEY (hanci_id) REFERENCES hanci(hanci_id)
        "manci_hanci_manci_id_fkey" FOREIGN KEY (manci_id) REFERENCES manci(manci_id)
    Code:
                                     Table "public.hanci"
      Column  |         Type          |                     Modifiers                      
    ----------+-----------------------+----------------------------------------------------
     hanci_id | integer               | not null default nextval('hanci_id_seq'::regclass)
     hanci    | character varying(50) | 
     da       | character varying(50) | 
     geo      | boolean               | 
     guan     | boolean               | 
     ren      | boolean               | 
    Indexes:
        "hanci_pkey" PRIMARY KEY, btree (hanci_id)
        "hanci_ju_key" UNIQUE, btree (hanci)

  13. #13
    Join Date
    Feb 2009
    Posts
    54
    Code:
       Table "public.manci_mancizu"
       Column   |  Type   | Modifiers 
    ------------+---------+-----------
     manci_id   | integer | not null
     mancizu_id | integer | not null
    Indexes:
        "manci_mancizu_pkey" PRIMARY KEY, btree (manci_id, mancizu_id)
    Foreign-key constraints:
        "manci_mancizu_manci_id_fkey" FOREIGN KEY (manci_id) REFERENCES manci(manci_id)
        "manci_mancizu_mancizu_id_fkey" FOREIGN KEY (mancizu_id) REFERENCES mancizu(mancizu_id)
    Code:
                                       Table "public.mancizu"
       Column   |          Type          |                      Modifiers                       
    ------------+------------------------+------------------------------------------------------
     mancizu_id | integer                | not null default nextval('mancizu_id_seq'::regclass)
     mancizu    | character varying(100) | 
     da         | character varying(50)  | 
    Indexes:
        "mancizu_pkey" PRIMARY KEY, btree (mancizu_id)
        "mancizu_cizu_key" UNIQUE, btree (mancizu)
    Code:
    Table "public.mancizu_hancizu"
       Column   |  Type   | Modifiers 
    ------------+---------+-----------
     mancizu_id | integer | not null
     hancizu_id | integer | not null
    Indexes:
        "mancizu_hancizu_pkey" PRIMARY KEY, btree (mancizu_id, hancizu_id)
    Foreign-key constraints:
        "mancizu_hancizu_hancizu_id_fkey" FOREIGN KEY (hancizu_id) REFERENCES hancizu(hancizu_id)
        "mancizu_hancizu_mancizu_id_fkey" FOREIGN KEY (mancizu_id) REFERENCES mancizu(mancizu_id)
    Code:
                                      Table "public.hancizu"
       Column   |         Type          |                      Modifiers                       
    ------------+-----------------------+------------------------------------------------------
     hancizu_id | integer               | not null default nextval('hancizu_id_seq'::regclass)
     hancizu    | character varying(50) | 
     da         | character varying(50) | 
    Indexes:
        "hancizu_pkey" PRIMARY KEY, btree (hancizu_id)
        "hancizu_cizu_key" UNIQUE, btree (hancizu)
    Code:
             Table "public.mancizu_mancizuda"
        Column    |         Type          | Modifiers 
    --------------+-----------------------+-----------
     mancizu_id   | integer               | not null
     mancizuda_id | integer               | not null
     zuga         | character varying(10) | 
    Indexes:
        "mancizu_mancizuda_pkey" PRIMARY KEY, btree (mancizu_id, mancizuda_id)
    Foreign-key constraints:
        "mancizu_mancizuda_mancizu_id_fkey" FOREIGN KEY (mancizu_id) REFERENCES mancizu(mancizu_id)
        "mancizu_mancizuda_mancizuda_id_fkey" FOREIGN KEY (mancizuda_id) REFERENCES mancizuda(mancizuda_id)

  14. #14
    Join Date
    Feb 2009
    Posts
    54
    Code:
              Table "public.manci_manju"
      Column  |         Type          | Modifiers 
    ----------+-----------------------+-----------
     manci_id | integer               | not null
     manju_id | integer               | not null
     juga     | character varying(10) | 
    Indexes:
        "manci_manju_pkey" PRIMARY KEY, btree (manci_id, manju_id)
    Foreign-key constraints:
        "manci_manju_manci_id_fkey" FOREIGN KEY (manci_id) REFERENCES manci(manci_id)
        "manci_manju_manju_id_fkey" FOREIGN KEY (manju_id) REFERENCES manju(manju_id)
    Code:
                                      Table "public.manju"
      Column  |          Type          |                     Modifiers                      
    ----------+------------------------+----------------------------------------------------
     manju_id | integer                | not null default nextval('manju_id_seq'::regclass)
     manju    | character varying(200) | 
    Indexes:
        "manju_pkey" PRIMARY KEY, btree (manju_id)
        "manju_ju_key" UNIQUE, btree (manju)
    Code:
       Table "public.manju_hanju"
      Column  |  Type   | Modifiers 
    ----------+---------+-----------
     manju_id | integer | not null
     hanju_id | integer | not null
    Indexes:
        "manju_hanju_pkey" PRIMARY KEY, btree (manju_id, hanju_id)
    Foreign-key constraints:
        "manju_hanju_hanju_id_fkey" FOREIGN KEY (hanju_id) REFERENCES hanju(hanju_id)
        "manju_hanju_manju_id_fkey" FOREIGN KEY (manju_id) REFERENCES manju(manju_id)
    Code:
                                         Table "public.hanju"
      Column  |          Type          |                        Modifiers                         
    ----------+------------------------+----------------------------------------------------------
     hanju_id | integer                | not null default nextval('hanju_hanju_id_seq'::regclass)
     hanju    | character varying(200) | 
    Indexes:
        "hanju_pkey" PRIMARY KEY, btree (hanju_id)
    Code:
              Table "public.manju_manjuda"
       Column   |         Type          | Modifiers 
    ------------+-----------------------+-----------
     manju_id   | integer               | not null
     manjuda_id | integer               | not null
     juga       | character varying(10) | 
    Indexes:
        "manju_manjuda_pkey" PRIMARY KEY, btree (manju_id, manjuda_id)
    Foreign-key constraints:
        "manju_manjuda_manju_id_fkey" FOREIGN KEY (manju_id) REFERENCES manju(manju_id)
        "manju_manjuda_manjuda_id_fkey" FOREIGN KEY (manjuda_id) REFERENCES manjuda(manjuda_id)

    Code:
                                            Table "public.manjuda"
        Column    |          Type          |                          Modifiers                           
    --------------+------------------------+--------------------------------------------------------------
     manjuda_id   | integer                | not null default nextval('manjuda_manjuda_id_seq'::regclass)
     manjuda      | integer                | 
     manjuda_text | character varying(100) | 
     juga         | character varying(10)  | 
    Indexes:
        "manjuda_pkey" PRIMARY KEY, btree (manjuda_id)

  15. #15
    Join Date
    Feb 2009
    Posts
    54
    Code:
       Table "public.manci_mancida"
       Column   |  Type   | Modifiers
    ------------+---------+-----------
     manci_id   | integer | not null
     mancida_id | integer | not null
    Indexes:
        "manci_mancida_pkey" PRIMARY KEY, btree (manci_id, mancida_id)
    Foreign-key constraints:
        "manci_mancida_manci_id_fkey" FOREIGN KEY (manci_id) REFERENCES manci(manci_id)
        "manci_mancida_mancida_id_fkey" FOREIGN KEY (mancida_id) REFERENCES mancida(mancida_id)
    Code:
                                            Table "public.mancida"
        Column    |          Type          |                          Modifiers
    --------------+------------------------+--------------------------------------------------------------
     mancida_id   | integer                | not null default nextval('mancida_mancida_id_seq'::regclass)
     mancida      | integer                |
     mancida_text | character varying(100) |
     ciga         | character varying(10)  |
    Indexes:
        "mancida_pkey" PRIMARY KEY, btree (mancida_id)

Posting Permissions

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