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

    Join Date
    Jun 2010
    Posts
    8
    Rep Power
    0

    A simple SELECT question, I am sure.


    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:
    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:
    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. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by jp128
    Do not repeat the family number or the parentís last and first name.
    do not attempt to achieve this with sql

    this is the job of the application layer
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo