Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2013
    Posts
    3

    Unanswered: Newbie Postgres query question

    Good morning all,

    I’ve been struggling with a query for the last few days and hope that someone out there might have some insight on why the results aren’t coming out as expected. Note: We can only use views and do not have access to the tables.

    First what we want to accomplish:
    We want to get a list of all our patrons that includes the following info:

    User record #
    Created date
    Exp Date
    PCode3
    Total checkout
    Total renewals
    Current Checkout
    Ptype
    Circ Active date
    Last updated date
    Home library

    The following query works fine for this and returns all 1,030,216 patrons which is equal to the number of rows in the “patron_view” view:

    SELECT
    pv.record_num AS Record_Number, --1
    DATE (rm.creation_date_gmt) AS Creation_Date, --2
    DATE (pv.expiration_date_gmt) AS Expiration_Date, --3
    pv.pcode3 AS PCode_3, --4
    pv.checkout_total AS Checkout_Total, --5
    pv.renewal_total AS Renewals, --6
    pv.checkout_count AS Current_Checkouts, --7
    (pv.ptype_code-1) AS PType, --8
    pn.description AS Description, --9
    DATE (pv.activity_gmt) AS Circ_Active, --10
    DATE (rm.record_last_updated_gmt) AS Last_Updated, --11
    pv.home_library_code AS Home_Library --12
    FROM
    sierra_view.patron_view pv
    JOIN
    sierra_view.record_metadata rm
    ON
    pv.record_type_code = rm.record_type_code AND
    pv.record_num = rm.record_num
    LEFT JOIN
    sierra_view.ptype_property_name pn
    ON
    pn.ptype_id = pv.ptype_code;

    However in anticipation of management wanting actual zip codes for our patrons I wanted to include the zip and this is where the issues arise. The numbers never come out to equal the number of actual patrons from the “patron_view” view. When the query completes the max number I get is the number of rows that are included in the “patron_record_address” view which is 1,027,759. I’ve tried using right and left joins, even full outer joins with no success. I also tried swapping the order of the joins and still cannot get the results to equal 1,030,216.


    SELECT
    pv.record_num AS Record_Number, --1
    DATE (rm.creation_date_gmt) AS Creation_Date, --2
    DATE (pv.expiration_date_gmt) AS Expiration_Date, --3
    pv.pcode3 AS PCode_3, --4
    pr.postal_code AS Zip_Code, -- 5 Column added for zip from last join statement
    pv.checkout_total AS Checkout_Total, --6
    pv.renewal_total AS Renewals, --7
    pv.checkout_count AS Current_Checkouts, --8
    (pv.ptype_code-1) AS PType, --9
    pn.description AS Description, --10
    DATE (pv.activity_gmt) AS Circ_Active, --11
    DATE (rm.record_last_updated_gmt) AS Last_Updated, --12
    pv.home_library_code AS Home_Library --13
    FROM
    sierra_view.patron_view pv
    JOIN
    sierra_view.record_metadata rm
    ON
    pv.record_type_code = rm.record_type_code AND
    pv.record_num = rm.record_num
    LEFT JOIN
    sierra_view.ptype_property_name pn
    ON
    pn.ptype_id = pv.ptype_code
    JOIN
    sierra_view.patron_record_address pr
    ON
    pr.patron_record_id = pv.id;

    I’m fairly new at writing queries, about two weeks so I’m hoping this is just a rookie mistake and someone has some insight as to what is wrong. Any advice is appreciated.

    Thanks in advance!

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ... I’ve tried using right and left joins, even full outer joins with no success.
    Where did you applied right and left joins?

    Did you tried like...
    Code:
     LEFT  OUTER JOIN
           sierra_view.patron_record_address pr
      ON   pr.patron_record_id = pv.id
    ;

  3. #3
    Join Date
    Sep 2013
    Posts
    3
    Thanks for the reply

    I tried the different joins on the last view patron_record_address:

    JOIN
    sierra_view.patron_record_address pr
    ON
    pr.patron_record_id = pv.id;

    Left, full outer and left outer joins gave me gave me 1,032,904 (+2688 rows)
    Join, Right, Inner, and Right Outer joins gave me 1,027,759 (-2457 rows)

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Join and Inner join are same operation.
    Left join and Left outer join are same operation.
    Right join and Right outer join are same operation.

    So, it's useless to try both of Join and Inner join, both of Left join and Left outer join or both of Right join and Right outer join.

    You'll find
    The words INNER and OUTER are optional in all forms. INNER is the default; LEFT, RIGHT, and FULL imply an outer join.
    in 7.2.1.1. Joined Tables ---> Qualified joins
    in PostgreSQL: Documentation: 9.2: Table Expressions


    By the way,
    what result did you got from this query?
    Note: Because, Consraints(Primary key, Uniqueness, Referential integrity, so on...) are not clear.
    Then some of counts in the query might be useless or other counts might have meaning.
    Code:
    SELECT pv.id
         , COUNT(*)                            AS count_rows
         , COUNT(DISTINCT rm.record_num)       AS count_dist_rm
         , COUNT(pr.patron_record_id)          AS count_pr
         , COUNT(DISTINCT pr.postal_code)      AS cnt_dist_postal_code
         , MIN(pr.postal_code)                 AS min_postal_code
         , MAX(pr.postal_code)                 AS max_postal_code
     FROM  sierra_view.patron_view           pv
     INNER JOIN
           sierra_view.record_metadata       rm
      ON   pv.record_type_code = rm.record_type_code
       AND pv.record_num       = rm.record_num
     LEFT  OUTER JOIN
           sierra_view.patron_record_address pr
      ON   pr.patron_record_id = pv.id
     GROUP BY
           pv.id
     HAVING
           COUNT(*) > 1
    ;

  5. #5
    Join Date
    Sep 2013
    Posts
    3
    Thanks for the reply.

    A colleague of mines was able to get the query to work. Turns out the data dictionary supplied to us is not correct and they had encountered a similar issue so they were able to give help.

    Thanks for the feedback though I appreciate it!

Posting Permissions

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