Filter a select, without duplicate SQL queries being returned

Question

The problem is to return a SELECT with only those schedules that are no longer accepted by the user. I’m trying to use the schedule table that has already been accepted, which has both the user ID and the schedule that he accepted to filter the schedule.

Table of accepted schedules

| SCHEDULE_ID |   USER_ID    |
------------------------------
      25      |      26      |
      41      |      26      |
      41      |      26      |
      42      |      29      |

The keys come from the table link with the following tables:

Schedule table created

SCHEDULE_ID |    TITULO    |  DAILY_PRICE  |  CLIENT_ID  |
----------------------------------------------------------
 41         |  Padaria     |    290.00     |      1      |
 42         |  Confeitaria |    350.00     |      2      |
 25         |  Lavanderia  |    350.00     |      3      |

List of Registered Deliverers

 USER_ID    |    NAME    |
--------------------------
 25         |   João     |
 26         |   Maria    |
 29         |   Claúdio  |

I’m not really used to using SQL statements yet, so I couldn’t develop a query that would return the values, without them being repeated for example.

Answer 1

You must perform a Join, the Join joins the results of two tables in one query, it uses a field to perform this join.

Example of desired junction

Select Entregadores.NAME,
       Agendamento.TITULO,
       Agendamento.DAILY_PRICE,
       Agendamento.CLIENT_ID 
  from Tabela_de_agendamentos_que_foram_aceitos AgendamentoAceito
   join Tabela_de_agendamentos_criados Agendamento
     on (AgendamentoAceito.SCHEDULE_ID = Agendamento.SCHEDULE_ID)
   join Tabela_de_entregadores_que_aceitaram_os_agendamentos Entregadores
     on (AgendamentoAceito.USER_ID = Entregadores.USER_ID)

With all Join well positioned the records will not repeat themselves, unless they are repeated in the tables.

Speaking a little more of John

Many developers have difficulty knowing which result is returned by each Join in SQL and therefore when they should use each one. To facilitate this understanding, Figure 1 brings a graphic representation, based on Set Theory, very well known in mathematics. In this image, we have the representation of two tables (A and B) and the expected result for each type of Join (the area in red represents the records returned by the query).

Source: http://www.devmedia.com.br/sql-join-entenda-como-funciona-o-retorno-dos-dados/31006

Answer 2

To reach the desired result you must use the clause EXISTS together with NOT:

SELECT a.*
  FROM agendamentos a
 WHERE NOT EXISTS(SELECT 1
                    FROM agendamento_aceitos aa
                   WHERE aa.schedule_id = a.schedule_id
                     AND aa.user_id = 26)

In the example above will be shown schedules that have not yet been accepted by the courier 26 - Maria.

---

Subqueries with EXISTS or NOT EXISTS

If a subquery Returns any Rows at all, EXISTS subquery is TRUE, and NOT EXISTS subquery is FALSE

Or in free translation:

If the subquery returns any line, EXISTS will BE TRUE, and NOT EXISTS will BE FALSE

Answer 3

Can use a distinct in the query to return unique values.