orting a table by the last_name from another table
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: orting a table by the last_name from another table

  1. #1
    Join Date
    Sep 2004
    Posts
    51

    orting a table by the last_name from another table

    I have a Publications table (see tables below for all tables). It has a publications_authors table that has a many-to-one relationship to publications because a publication may have many authors. In addition, in Authors, either id can be filled with a number that relates to the People table, or with fname, lname if the person isn't
    in the People table.

    Each publication will have multiple authors and it should only sort the list of publications by the lastname,firstname of the first author (see example). The authors for each publication must display in the order that they were entered though, so I thought I could concatenate them, then maybe sort.

    example:
    benas, j.,achary,q The Mountains of Schoenberg
    zaine,p,benas,j The forest of my mind

    Here's my current query:
    SELECT publication_name,publicationa_id,lastname || ':' || firstname
    FROM (

    SELECT p.publication_name,
    CASE WHEN o.lname is not null then o.lname ELSE a.a_lname END as lastname,
    CASE WHEN o.fname is not null THEN o.fname ELSE a.a_fname END as firstname, a.publicationa_id

    FROM publications p INNER JOIN publications_authors a ON p.publication_id = a.publication_id
    LEFT JOIN people o ON a.id = o.id
    ORDER BY publicationa_id asc
    )


    and the results:
    http://hhp.umd.edu/research/publications_display.cfm

    and I don't seem to be able to add the ',' when I concatenate the lastname and first name.


    create table publications (
    publication_id NUMBER Primary Key,
    id NUMBER REFERENCES people(id),
    date_added DATE,
    date_modified DATE,
    published_day NUMBER,
    published_month NUMBER,
    published_year NUMBER,
    publication_name VARCHAR2(300), // designates the name of the book/
    journal that the publication may be in
    title VARCHAR2(300),
    upload_path VARCHAR2(200)
    )


    create table publications_authors (
    publicationa_id NUMBER Primary Key,
    publication_id NUMBER REFERENCES publications(publication_id) ON
    DELETE CASCADE,
    id NUMBER REFERENCES people(id),
    a_fname VARCHAR2(100),
    a_mname VARCHAR2(20),
    a_lname VARCHAR2(100)
    )

    create table PEOPLE (
    id NUMBER Primary Key,
    dateModified date,
    type VARCHAR2(50),
    fname VARCHAR2(100),
    mname VARCHAR2(100),
    lname VARCHAR2(100),
    status VARCHAR2(20)
    )

    thank you for any assistance.

    daniel

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    so... what was your question?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Sep 2004
    Posts
    51

    orting a table by the last_name from another table

    How do I sort the list of publications by the last name of the first author?

    If it's an improper questions, just say so.

  4. #4
    Join Date
    Sep 2004
    Posts
    51
    and I suppose I'd also like to know why I can't use a comma, or how to use a comma, when I'm concatenating two fields.

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by danielkessler
    If it's an improper questions, just say so
    Relax, all questions are proper...

    Quote Originally Posted by danielkessler
    How do I sort the list of publications by the last name of the first author?
    What tells you in your data structure which one is the first author?

    Quote Originally Posted by danielkessler
    and I suppose I'd also like to know why I can't use a comma, or how to use a comma, when I'm concatenating two fields
    There is nothing wrong with it, try something like...

    select a_fname || ',' a_lname
    from publications_authors
    where rownum < 2;

    ... just to test it.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  6. #6
    Join Date
    Sep 2004
    Posts
    51
    Quote Originally Posted by PAVB
    Relax, all questions are proper...
    I'm sorry, but it's morning and I didn't state that well.

    What I meant is that I have little experience working with datasets from related tables and often, I don't know what can and cannot be done. So, I don't know if I'm asking something that's clearly not doable or shouldn't be done by the DB.

    Quote Originally Posted by PAVB
    What tells you in your data structure which one is the first author?
    It's the order that they were entered, so it's the UID publicationa_id

    Quote Originally Posted by PAVB
    There is nothing wrong with it, try something like...

    select a_fname || ',' a_lname
    from publications_authors
    where rownum < 2;

    ... just to test it.
    That works, but doesn't bring in a_lname because there's not a pipe after the comma - but the comma does the right thing.
    I had a double pipe: a_fname || ',' || a_lname and that works with a colon in between, but not with a comma. I receive the error:
    [Table (rows 8 columns A_FNAME||','||A_LNAME): [A_FNAME||','||A_LNAME: coldfusion.sql.QueryColumn@429784] ] is not indexable by A_FNAME||'

  7. #7
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by danielkessler
    That works, but doesn't bring in a_lname because there's not a pipe after the comma - but the comma does the right thing
    I'm sorry, I forgot to type the pipe after ','

    Not reason for it not to work... a ColdFusion issue perhaps?... can you trick ColdFusion to show you the query is trying to submit?

    Quote Originally Posted by danielkessler
    It's the order that they were entered, so it's the UID publicationa_id
    So... if a publication has three authors you will have three entries in publications_authors, all of them with the same publicationa_id value, am I right?

    If YES... you do not have an order there, Oracle might retrive first any of the three entries matching the key.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  8. #8
    Join Date
    Sep 2004
    Posts
    51
    Quote Originally Posted by PAVB
    I'm sorry, I forgot to type the pipe after ','

    Not reason for it not to work... a ColdFusion issue perhaps?... can you trick ColdFusion to show you the query is trying to submit?
    The query is pretty straight-up since I'm just doing a test query for the comma, as you suggested. I added debug="yes" result="queryResult" and it does a dump of the sql. It's:
    SELECT a_fname || ',' || a_lname FROM publications_authors
    The dump of the query is here (it's the one in blue):
    http://hhp.umd.edu/research/publications_display.cfm


    Quote Originally Posted by PAVB
    So... if a publication has three authors you will have three entries in publications_authors, all of them with the same publicationa_id value, am I right?

    If YES... you do not have an order there, Oracle might retrive first any of the three entries matching the key.
    No, they have incrementing publicationa_id values, using nextVal. They all have the same publication_id from the publications table. So I want to sort all those publications in the publications table by the first author's last name from the publications_authors table.

    Honestly, I'm not even sure how to work with the dataset once I get it since it'll show 3 entries for 3 authors for each publication (for example).

    I know related tables are surely the way to go for flexibility, but I'm often left scatching my head when using them.

  9. #9
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Lets try to solve the issues one at a time.

    Query below should serve as a template for what you want, could you please try it?

    select p.publication_id,
    p.title,
    a.a_lname
    from publications p,
    publications_author a
    where p.publication_id = a.publication_id
    a.publicationa_id =
    (select min(aa.publicationa_id)
    from publications_author aa
    where p.publication_id = aa.publication_id
    )
    order by p.title, a.a_lname
    ;


    In regards to the comma issue... either I'm missing something really big or it's related ColdFusion, don't know why. In your case I would try some ColdFusion forum and check for the comma issue.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  10. #10
    Join Date
    Sep 2004
    Posts
    51
    alright. That query shows promise, but in my testing I found that I'm doing some things incorrect elsewhere so I've not been able to check it yet. I'm testing now.

    As for the "concatenate with a comma" issue, I posted to cf-talk (www.houseoffusion) which is a really good list, and they said that I need to alias the output for it to work. So:
    a_fname || ',' || a_lname
    became
    a_fname || ',' || a_lname as the_name

    and it worked.

Posting Permissions

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


Click Here to Expand Forum to Full Width