I have a system in which "sources" generates tickets, and then send them somewhere.
In my postgres db I have 3 tables:
tickets AS j
: information about goods. columns: id_ticket, idsource, date.
source AS b
: information about the source. columns: id_source.
register AS c
: information about the sending state of a ticket. columns: id_ticket, idsource, date.
NOTE: pay attention to these fields: b.id_source
, c.idsource
, j.idsource
The ticket elements are stored inside tickets AS j
when the ticket is generated.
Every source can generate at maximum one ticket per day.
Somedays the ticket generation fails, so that day no data will be stored in tickets AS j
, so there will be no ticket having j.date=that_day
Every source at the end of the day tryes to send the tickets generated that day to some other place.
register AS c
, so there will be no ticket having c.date=that_day
register AS c
is filled with a record with the data coming from tickets AS j
, and c.sent
is set to 0
. (scenario: sending failed)register AS c
is filled with a record with the data coming from tickets AS j
, and c.sent
is set to 1
. (scenario: sending succeeded)Every day I would like to know which scenario happened for each source.
In order to do so, I can only work by select queries and I cannot create more tables.
Furthermore, to have this information I would like to run only one query, giving me the global view on the source behaviour of that day.
I have developed the following query, which returns all results for scenario "sending succeeded":
for all the sources that succeeded to send the ticket, it displays the source id and the state of the ticket, and it does not return any record both in case the sending failed or in case the ticket generation failed.
SELECT b.id_source, c.sent
FROM register AS c
JOIN tickets AS j ON c.date=j.date and c.id_ticket=j.id_ticket and c.idsource=j.idsource
LEFT JOIN source AS b ON j.idsource=b.id_source
WHERE c.date=current_date-1;
I would like to manipulate and couple this query with
SELECT b.id_source
FROM source AS b;
in order to get an output like:
if ticket generation failed: display b.id_source, display void data for c.sent
if ticket generation succeeded and sending failed: display b.id_source, display c.sent (0)
if ticket generation succeeded and sending failed: display b.id_source, display c.sent (1)
This would be a sort of checklist.
In order to do so, I tried to substitute the JOIN and the LEFT JOIN with a FULL OUTER JOIN
SELECT b.id_source, c.sent
FROM register AS c
FULL OUTER JOIN tickets AS j ON c.date=j.date and c.id_ticket=j.id_ticket and c.idsource=j.idsource
FULL OUTER JOIN source AS b ON j.idsource=b.id_source
WHERE c.date=current_date-1;
but it seems it does not work, or maybe I am picking the incorrect data to do the test.
Which query could do the job?
I understand that for a given date, you want one row for each source, and columns that tell whether a ticket was properly generated and sent.
If so, I would recommend a left join
starting from the source
table; obviously a ticket needs to be created before it can be sent, so that's the order we'll follow for the joins:
select b.id, c.sent
from source as b
left join tickets as j
on j.idsource = b.idsource
and j.date = current_date - 1
left join register as c
on c.idsource = j.idsource
and c.date = j.date
and c.id_ticket = j.id_ticket
This guarantees one row per source; note that I moved the date filtering to the on
clause of the left join
, so that sources without a daily ticket are not filtered out.
If we wanted to get the same result over a given period of time (so one row per date and per source), we could cross join
the sources with a date series. This would do the check for the last 7 days:
select b.id, d.dt, c.sent
from source as b
cross join generate_series(current_date - interval '1 week', current_date, interval '1 day') d(dt)
left join tickets as j
on j.idsource = b.idsource
and j.date = d.dt
left join register as c
on c.idsource = j.idsource
and c.date = j.date
and c.id_ticket = j.id_ticket
order by d.id, d.dt