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!