#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2010
    Posts
    8
    Rep Power
    0

    Probably simple SELECT question...


    Hey everyone, I am working on some homework for a final project with SQL. I normally don't mind SQL, but there are very precise requirements for some select statements.

    I have two tables, all fiction, for parent and patient. I need to do a select that has "Patient List Report. Display the family number, parent's last and first name separated by a comma, patient's first name, and patient's last name. Sort by family number and patient's first name. Do not repeat the family number or the parentís last and first name.

    Here is my SQL so far...

    Code:
    SELECT parent.famnum, (parent.last || ', ' || parent.first) AS name, patient.first, patient.last FROM parent, patient WHERE parent.id = patient.parent ORDER BY parent.famnum, patient.first ASC;
    This gives me something like: minus all of the hyphens
    famnum-----name------------------first -------last
    ALTE01-----Alterio, Tyler-----------George-----Alterio
    ALTE01-----Alterio, Tyler-----------Scotty-----Alterio
    BALL01-----Ballenger, Timothy------Donald-----Ballenger
    BALL01-----Ballenger, Timothy------Thomas----Ballenger


    But I need to have it like:
    famnum-----name------------------first -------last
    ALTE01-----Alterio, Tyler-----------George-----Alterio
    -----------------------------------Scotty-----Alterio
    BALL01-----Ballenger, Timothy------Donald-----Ballenger
    -----------------------------------Thomas----Ballenger


    The SQL for table create/populate:

    Code:
    CREATE TABLE parent ( id integer NOT NULL, famnum text NOT NULL, first text NOT NULL, last text NOT NULL, phone text NOT NULL, address text NOT NULL, city text NOT NULL, state text NOT NULL, zip text NOT NULL, insurance text ); CREATE TABLE patient ( id integer NOT NULL, mrn text NOT NULL, first text NOT NULL, last text NOT NULL, parent integer NOT NULL, insurance text ); INSERT INTO parent VALUES (1, 'ALTE01', 'Tyler', 'Alterio', '2073550795', '727 Willow Bank St', 'Hamilton', 'ME', '06823', 'HA'); INSERT INTO parent VALUES (2, 'BALL01', 'Timothy', 'Ballenger', '2079749022', '2830 Linden St # 3F', 'Boalsburg', 'ME', '08017', ''); INSERT INTO parent VALUES (3, 'BISH01', 'Richard', 'Bishop', '2073551358', '1279 Zion Rd', 'Hamilton', 'ME', '06823', ''); INSERT INTO parent VALUES (4, 'DECK01', 'Wilson', 'Decker', '2076928178', 'RD #2 Box 410-D', 'Hamilton', 'ME', '06823', ''); INSERT INTO parent VALUES (6, 'McCr01', 'Gene ', 'McCray', '2073550020', '911 Green Ave', 'Hamilton', 'ME', '06823', 'HA'); INSERT INTO parent VALUES (7, 'Smit01', 'Jenny', 'Smith', '2076929278', '45 West End Rd', 'Elkhorn', 'ME', '06001', 'MA'); INSERT INTO parent VALUES (8, 'Rich01', 'Betty', 'Richardson', '2073558565', '67 Quarry Rd', 'Elkhorn', 'ME', '06001', 'HA'); INSERT INTO parent VALUES (9, 'Robe01', 'Boris', 'Roberson', '2072370519', '1316 Charles St', 'Maryville', 'ME', '06801', 'MA'); INSERT INTO parent VALUES (10, 'Wats01', 'Jane', 'Watson ', '2072370555', '777 Fox Blvd Apt 445', 'Maryville', 'ME', '06801', ''); INSERT INTO parent VALUES (11, 'Well01', 'Franklin', 'Wellard ', '2077777777', '350 West Hunter Ave', 'Boalsburg', 'ME', '08017', ''); INSERT INTO parent VALUES (12, 'Wils01', 'Prescott', 'Wilson ', '2077777780', '222 Ocean View Dr', 'Elkhorn', 'ME', '06001', 'HA'); INSERT INTO parent VALUES (5, 'Flag01', 'Wilma', 'Flagstone', '2073559279', '65 Quarry Rd', 'Elkhorn', 'ME', '06001', 'BC'); INSERT INTO patient VALUES (1, 'BALT01', 'Thomas', 'Ballenger', 2, ''); INSERT INTO patient VALUES (2, 'DECC01', 'Chad', 'Decker', 4, ''); INSERT INTO patient VALUES (3, 'SMIJ01', 'Justin', 'Smith', 4, ''); INSERT INTO patient VALUES (4, 'SMIM01', 'Megan', 'Smith', 4, ''); INSERT INTO patient VALUES (5, 'DECN01', 'Newborn', 'Decker', 4, ''); INSERT INTO patient VALUES (6, 'MCCA01', 'Angela', 'McCray', 6, '111223333-02'); INSERT INTO patient VALUES (7, 'MCCT01', 'Taylor', 'McCray', 6, '111223333-01'); INSERT INTO patient VALUES (8, 'BALD01', 'Donald', 'Ballenger', 2, ''); INSERT INTO patient VALUES (9, 'WELN01', 'Natalie', 'Wellard', 11, ''); INSERT INTO patient VALUES (10, 'BISA01', 'Alison', 'Bishop', 3, ''); INSERT INTO patient VALUES (11, 'ROBD01', 'Dennis', 'Roberson', 9, '111334444-01'); INSERT INTO patient VALUES (12, 'ROBC01', 'Carol', 'Roberson', 9, '111334444-02'); INSERT INTO patient VALUES (13, 'ALTS01', 'Scotty', 'Alterio', 1, '222113333-01'); INSERT INTO patient VALUES (14, 'ALTG01', 'George', 'Alterio', 1, '222113333-02'); INSERT INTO patient VALUES (15, 'BISM01', 'Mary Ann', 'Bishop', 3, ''); INSERT INTO patient VALUES (16, 'ROBM01', 'Mary', 'Roberson', 9, '111334444-03'); INSERT INTO patient VALUES (17, 'ROBJ01', 'John', 'Roberson', 9, '111334444-04'); INSERT INTO patient VALUES (18, 'ROBP01', 'Patrick', 'Roberson', 9, '111334444-05'); INSERT INTO patient VALUES (19, 'WELB01', 'Bert', 'Wellard', 11, ''); INSERT INTO patient VALUES (20, 'WILD01', 'David', 'Wilson', 12, '333221111-01'); INSERT INTO patient VALUES (21, 'SMIN01', 'Newborn', 'Smith', 7, '111332222-01'); INSERT INTO patient VALUES (22, 'FLAP01', 'Pebbles', 'Flagstone', 5, '333552222-01'); INSERT INTO patient VALUES (23, 'RICB01', 'Ben', 'Richardson', 8, '444113333-01'); INSERT INTO patient VALUES (24, 'WATJ01', 'Judy', 'Watson', 10, ''); INSERT INTO patient VALUES (25, 'WATL01', 'Leroy', 'Watson', 10, '');
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,784
    Rep Power
    348
    Stuff like that is usually done in the front-end application rather than in SQL code.

    But here is one solution using window functions:

    Code:
    SELECT case 
               when row_number() over (wname) = 1 then parent.famnum
               else null
           end as famnum,
           case 
             when row_number() over (wname) = 1 then concat_ws(', ', parent.last, parent.first) 
             else null
           end AS name, 
           patient.first, 
           patient.last 
    FROM parent
      JOIN patient ON parent.id = patient.parent 
    WINDOW wname as (partition by parent.famnum order by concat_ws(', ', parent.last, parent.first))
    ORDER BY parent.famnum, concat_ws(', ', parent.last, parent.first);
    I changed some minor things from your original query
    • Using explicit JOIN syntax rather than the outdated implicit joins (in the WHERE clause)
    • Use concat_ws() instead of the || operator to handle NULL values properly. If neither parent.last nor parent.first can ever be NULL your solution will work just as well.

    More details about window functions can be found in the manual: http://www.postgresql.org/docs/9.2/s...al-window.html

    Here is a little SQLFiddle example showing the output: http://sqlfiddle.com/#!12/4891f/1

    Btw: thanks for posting a complete test-bed that really is helpful. It's even better if you post it as a SQLFiddle the next time.
    Last edited by shammat; June 25th, 2013 at 04:00 AM.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2010
    Posts
    8
    Rep Power
    0
    Thanks a ton. I told my professor that this is easily done on the application side, but he wanted it this way :O


    Thanks again
  6. #4
  7. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Hi,

    saying that this should be done in the application is an understatement.

    Your professor doesn't understand the purpose of database systems at all. I guess he's more used to Excel. But a relational DBMS is no Excel (even though it has tables). It's supposed to hold and provide data, not format pretty tables.

    When people don't understand this, they come up with nonsense tasks like this.

    What's the next step? Have the PostgreSQL terminal colorize the "cells" and display the content with Comic Sans 48 pt?
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".

IMN logo majestic logo threadwatch logo seochat tools logo