I’ve tried to find a reason for only one record in a table list of 8 other records that duplicates itself. I believe this is usually caused by joins that may have a duplicate id. Or something like that…
However, I am having trouble finding a column that might have two IDs or whatever that might cause this anomaly.
Any suggestions of where to look or how to trouble-shoot this?
Thanks!
I’m afraid that we will need a bit more relevant information to help you out. Maybe start by showing a few screen shots of the problem area.
Thanks Ben!
This is a screenshot of the report. Please note the middle two records with Philadelphia, MS that is duplicated. No other duplicates exists in the list.
I can now see the result, but we need to see what is causing it. Can you show us the query?
Due to the query length (three tables joined), I believe it might be better to put the api file here. Would that help?
Let’s try it.
…
require(’…/…/…/dmxConnectLib/dmxConnect.php’);
$app = new \lib\App();
$app->define(<<<‘JSON’
{
“settings”: {
“options”: {}
},
“meta”: {
“options”: {},
“$_GET”: [
{
“type”: “text”,
“name”: “get_id”
}
]
},
“exec”: {
“steps”: [
“Connections/dbh_data”,
“SecurityProviders/user_security”,
{
“name”: “list_report”,
“module”: “dbconnector”,
“action”: “select”,
“options”: {
“connection”: “dbh_data”,
“sql”: {
“type”: “SELECT”,
“columns”: [
{
“table”: “pastor”,
“column”: “district”
},
{
“table”: “pastor”,
“column”: “position”
},
{
“table”: “pastor”,
“column”: “first_name”
},
{
“table”: “pastor”,
“column”: “last_name”
},
{
“table”: “church”,
“column”: “church_city”
},
{
“table”: “church”,
“column”: “church_state”
},
{
“table”: “service”,
“column”: “faith_promise”
},
{
“table”: “service”,
“column”: “service_cancel”
},
{
“table”: “service”,
“column”: “service_cancel_reason”
},
{
“table”: “service”,
“column”: “offering_only”
},
{
“table”: “service”,
“column”: “offering_amount”,
“aggregate”: “SUM”,
“alias”: “offeringAmount”
},
{
“table”: “service”,
“column”: “pim_amount”,
“aggregate”: “SUM”,
“alias”: “pimAmount”
},
{
“table”: “service”,
“column”: “how_many_pims”,
“aggregate”: “SUM”,
“alias”: “howManyPims”
},
{
“table”: “service”,
“column”: “project_amount”,
“aggregate”: “SUM”,
“alias”: “projectAmount”
},
{
“table”: “church”,
“column”: “service_date”,
“aggregate”: “”
},
{
“table”: “service”,
“column”: “field_pim_amount”,
“alias”: “fieldPimAmount”,
“aggregate”: “SUM”
},
{
“table”: “expense”,
“column”: “auto_miles”,
“alias”: “autoMiles”,
“aggregate”: “SUM”
},
{
“table”: “expense”,
“column”: “auto_expense”,
“alias”: “autoExpense”,
“aggregate”: “SUM”
},
{
“table”: “expense”,
“column”: “auto_expense_descript”
},
{
“table”: “expense”,
“column”: “other_expense”,
“alias”: “otherExpense”,
“aggregate”: “SUM”
},
{
“table”: “expense”,
“column”: “other_expense_descript”
},
{
“table”: “pastor”,
“column”: “pastor_id”
},
{
“table”: “church”,
“column”: “pastor_id”
},
{
“table”: “service”,
“column”: “pastor_id”
},
{
“table”: “expense”,
“column”: “pastor_id”
},
{
“table”: “church”,
“column”: “am_pm”
}
],
“table”: {
“name”: “pastor”
},
“joins”: [
{
“table”: “church”,
“column”: “",
“type”: “RIGHT”,
“clauses”: {
“condition”: “AND”,
“rules”: [
{
“table”: “church”,
“column”: “pastor_id”,
“operator”: “equal”,
“value”: {
“table”: “pastor”,
“column”: “pastor_id”
},
“operation”: “=”
}
]
}
},
{
“table”: “service”,
“column”: "”,
“type”: “RIGHT”,
“clauses”: {
“condition”: “AND”,
“rules”: [
{
“table”: “service”,
“column”: “pastor_id”,
“operator”: “equal”,
“value”: {
“table”: “pastor”,
“column”: “pastor_id”
},
“operation”: “=”
}
]
}
},
{
“table”: “expense”,
“column”: “",
“type”: “RIGHT”,
“clauses”: {
“condition”: “AND”,
“rules”: [
{
“table”: “expense”,
“column”: “pastor_id”,
“operator”: “equal”,
“value”: {
“table”: “pastor”,
“column”: “pastor_id”
},
“operation”: “=”
}
]
}
}
],
“wheres”: {
“condition”: “AND”,
“rules”: [
{
“id”: “pastor.itinerary_id”,
“field”: “pastor.itinerary_id”,
“type”: “double”,
“operator”: “equal”,
“value”: “{{$_GET.get_id}}”,
“data”: {
“table”: “pastor”,
“column”: “itinerary_id”,
“type”: “number”
},
“operation”: “=”
}
],
“conditional”: null,
“valid”: true
},
“query”: "SELECT pastor.district, pastor.position, pastor.first_name, pastor.last_name, church.church_city, church.church_state, service.faith_promise, service.service_cancel, service.service_cancel_reason, service.offering_only, SUM(service.offering_amount) AS offeringAmount, SUM(service.pim_amount) AS pimAmount, SUM(service.how_many_pims) AS howManyPims, SUM(service.project_amount) AS projectAmount, church.service_date, SUM(service.field_pim_amount) AS fieldPimAmount, SUM(expense.auto_miles) AS autoMiles, SUM(expense.auto_expense) AS autoExpense, expense.auto_expense_descript, SUM(expense.other_expense) AS otherExpense, expense.other_expense_descript, pastor.pastor_id, church.pastor_id, service.pastor_id, expense.pastor_id, church.am_pm\nFROM pastor\nRIGHT JOIN church ON (church.pastor_id = pastor.pastor_id) RIGHT JOIN service ON (service.pastor_id = pastor.pastor_id) RIGHT JOIN expense ON (expense.pastor_id = pastor.pastor_id)\nWHERE pastor.itinerary_id = :P1 / {{$_GET.get_id}} */\nGROUP BY pastor.district, pastor.position, pastor.first_name, pastor.last_name, church.church_city, church.church_state, service.faith_promise, service.service_cancel, service.service_cancel_reason, service.offering_only, church.service_date, expense.auto_expense_descript, expense.other_expense_descript, pastor.pastor_id, church.pastor_id, service.pastor_id, expense.pastor_id, church.am_pm\nORDER BY church.service_date ASC, church.service_time ASC, church.am_pm ASC”,
“params”: [
{
“operator”: “equal”,
“type”: “expression”,
“name”: “:P1”,
“value”: “{{$_GET.get_id}}”
}
],
“orders”: [
{
“table”: “church”,
“column”: “service_date”,
“direction”: “ASC”,
“recid”: 1
},
{
“table”: “church”,
“column”: “service_time”,
“direction”: “ASC”,
“recid”: 2
},
{
“table”: “church”,
“column”: “am_pm”,
“direction”: “ASC”,
“recid”: 3
}
],
“groupBy”: [
{
“table”: “pastor”,
“column”: “district”
},
{
“table”: “pastor”,
“column”: “position”
},
{
“table”: “pastor”,
“column”: “first_name”
},
{
“table”: “pastor”,
“column”: “last_name”
},
{
“table”: “church”,
“column”: “church_city”
},
{
“table”: “church”,
“column”: “church_state”
},
{
“table”: “service”,
“column”: “faith_promise”
},
{
“table”: “service”,
“column”: “service_cancel”
},
{
“table”: “service”,
“column”: “service_cancel_reason”
},
{
“table”: “service”,
“column”: “offering_only”
},
{
“table”: “church”,
“column”: “service_date”
},
{
“table”: “expense”,
“column”: “auto_expense_descript”
},
{
“table”: “expense”,
“column”: “other_expense_descript”
},
{
“table”: “pastor”,
“column”: “pastor_id”
},
{
“table”: “church”,
“column”: “pastor_id”
},
{
“table”: “service”,
“column”: “pastor_id”
},
{
“table”: “expense”,
“column”: “pastor_id”
},
{
“table”: “church”,
“column”: “am_pm”
}
]
}
},
“output”: true,
“meta”: [
{
“name”: “district”,
“type”: “text”
},
{
“name”: “position”,
“type”: “text”
},
{
“name”: “first_name”,
“type”: “text”
},
{
“name”: “last_name”,
“type”: “text”
},
{
“name”: “church_city”,
“type”: “text”
},
{
“name”: “church_state”,
“type”: “text”
},
{
“name”: “faith_promise”,
“type”: “text”
},
{
“name”: “service_cancel”,
“type”: “text”
},
{
“name”: “service_cancel_reason”,
“type”: “text”
},
{
“name”: “offering_only”,
“type”: “text”
},
{
“name”: “offeringAmount”,
“type”: “number”
},
{
“name”: “pimAmount”,
“type”: “number”
},
{
“name”: “howManyPims”,
“type”: “number”
},
{
“name”: “projectAmount”,
“type”: “number”
},
{
“name”: “service_date”,
“type”: “date”
},
{
“name”: “fieldPimAmount”,
“type”: “number”
},
{
“name”: “autoMiles”,
“type”: “number”
},
{
“name”: “autoExpense”,
“type”: “number”
},
{
“name”: “auto_expense_descript”,
“type”: “text”
},
{
“name”: “otherExpense”,
“type”: “number”
},
{
“name”: “other_expense_descript”,
“type”: “text”
},
{
“name”: “pastor_id”,
“type”: “number”
},
{
“name”: “am_pm”,
“type”: “text”
}
],
“outputType”: “array”
}
]
}
}
JSON
);
…
You have got this column name a few times. This is bad news. I usually prefix the table name to each of my column names e.g. churchPastorID and servicePastorID and churchAMPM
Ah… Yes, I recall that could happen if there was more than one similar name. Would setting an alias in the query work to cure this?
Isn’t it odd that only one record would repeat itself. Usually it seems that the whole list would repeat. Like the whole list would repeat for total rows within a table.
I will start practicing your naming syntax. Your suggested syntax will help tremendously.
When I removed the expense table join, the duplicate disappeared.
Try renaming the pastor_id column name for the expense table and try again.
Okay. I’ll give it a try.
I suspect that this may arise from a join where the join is being able to join to two records and the parnet table element is not flagged as distinct.#
Are there joins in the query and if so look for the possibility of that entry joining with two separate foreign keys
Well, I finally found the error. It was an extra entry in one of the four joined tables. Yeah, I am glad I found it, but not happy that I had missed it for about four or five times of looking at the tables.
Thanks for your help!
