PostgreSQL joins in a separate article
I'm making a join between two tables in PostgreSQL, one has a primary key constraint on the incident number (opentickets), while the other table has no constraints and may have duplicate incidents (incommingtickets). The problem occurs when trying to filter out duplicates. Request,
SELECT incommingtickets.*
FROM incommingtickets
LEFT JOIN opentickets
ON incommingtickets.incidentnumber = opentickets.incidentnumber
WHERE opentickets.incidentnumber IS NULL
AND incommingtickets.status NOT IN ('Closed','Cancelled', '')
works until it hits the duplicate, I get a primary key violation message. If I add a separate clause like
SELECT DISTINCT ON (incommingtickets.incidentnumber) incommingtickets.*
FROM incommingtickets
LEFT JOIN opentickets
ON incommingtickets.incidentnumber = opentickets.incidentnumber
WHERE opentickets.incidentnumber IS NULL
AND incommingtickets.status NOT IN ('Closed','Cancelled', '')
I am getting the error,
pg_query (): query failed: ERROR: missing FROM-clause entry for table "incommingtickets" LINE 30: WHERE opentickets.incidentnumber = incommingtickets.incident ...
source to share
Use a WHERE clause that filters out duplicates that you don't want, although I'm not very clear on why you want to join a "metric" like ticket count.
SELECT incommingtickets.*
FROM incommingtickets
WHERE incommingtickets.incidentnumber not in (
select
distinct
incidentnumber
FROM opentickets)
AND incommingtickets.status NOT IN ('Closed','Cancelled', '')
This way you are creating duplicates between both tables.
If you want to check or update the ticket status of any tickets in the table opentickets
, try getting from the incommingtickets
maximum status, for example:
WITH ticket_rows AS(
SELECT
rank() OVER (PARTITION BY ticket_id ORDER BY ticket_timestamp desc) as row_number,
ticket_id,
ticket_status,
ticket_timestamp
from incommingtickets
)
SELECT incommingtickets.*, opentickets_2.*
FROM opentickets o
LEFT JOIN ticket_rows ON ticket_rows.ticket_id= opentickets.ticket_id AND ticket_rows.row__number=1
If these are not your goals, please explain a little better what you are trying to achieve with this left join.
source to share