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.

• If one day there is no ticket of that day to send, nothing happens. (scenario: no ticket to send)
  So that day no data will be stored in register AS c, so there will be no ticket having c.date=that_day
  
• If one day there is a ticket of that day to send, and the sending fails, the table 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)
  
• If one day there is a ticket of that day to send, and the sending succeeds, the table 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?

CodePudding user response：

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 of the 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