SQL: selecting data from a table that doesn't contain value from another table

Question

CREATE TABLE `entidade_pessoa_avaliacao` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `idEntidade_pessoa` int(10) unsigned NOT NULL,
  `idSemana` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idEscola_Matriculado_idSemana` (`idEntidade_pessoa`,`idSemana`),
  KEY `idEscola_Matriculado` (`idEntidade_pessoa`),
  KEY `idSemana` (`idSemana`),
  CONSTRAINT `FK_smsescola_escola_acao_smsescola_escola_matriculado` FOREIGN KEY (`idEntidade_pessoa`) REFERENCES `entidade_pessoa` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_smsescola_escola_matriculado_avaliacao_smsescola_semana` FOREIGN KEY (`idSemana`) REFERENCES `semana` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=115 DEFAULT CHARSET=utf8;

INSERT INTO `entidade_pessoa_avaliacao` (`id`, `idEntidade_pessoa`, `idSemana`) VALUES
    (1, 1, 1),
    (2, 2, 1),
    (3, 3, 1),
    (4, 1, 2),
    (5, 2, 2),
    (6, 3, 2);

CREATE TABLE  `semana` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `nome_curto` varchar(250) NOT NULL,
  `inicio` date NOT NULL COMMENT,
  `termino` date NOT NULL COMMENT,
  `idDimensao` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idDimensao` (`idDimensao`)
) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8;

INSERT INTO `semana` (`id`, `nome_curto`, `inicio`, `termino`) VALUES
    (1, 'Faltas', '2017-10-24', '2017-11-14'),
    (2, 'Tarefas','2017-11-07', '2017-11-14'),
    (3, 'Participacao','2017-11-07', '2017-11-14');

How do I get which WEEK isn't in the student_avaliation table based on the date?

For example, which week wasn't registered where week.end='14/11/2017'.

I have to use NOT EXISTS but I don't know how.

Would you post anything you've tried, or at least the select without the NOT EXISTS? — Vinnie, Nov 10 '17 at 13:02
Show us db schema, sample data, current and expected output. Please read [**How-to-Ask**](http://stackoverflow.com/help/how-to-ask) And here is a great place to [**START**](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) to learn how improve your question quality and get better answers. [**How to create a Minimal, Complete, and Verifiable example**](http://stackoverflow.com/help/mcve) — Juan Carlos Oropeza, Nov 10 '17 at 13:07
still dont see your desire output or the explain the logic about how use the date :( — Juan Carlos Oropeza, Nov 10 '17 at 13:34
[SQL - find records from one table which don't exist in another](https://stackoverflow.com/q/367863/3404097) — philipxy, May 06 '23 at 13:25

score 0 · Answer 1 · edited May 06 '23 at 07:25

0

SELECT * FROM (SELECT A.ID,A.START,A.ENDDATE,B.ID Z FROM WEEK A
LEFT OUTER JOIN student_avaliation B
ON A.ID=B.IDWEEK) AA
WHERE AA.Z IS NULL
AND AA.ENDDATE='14/11/2017';

OR

select a.* from week a where not exists  (select * from student_avaliation b
where a.id=b.idweek);

edited May 06 '23 at 07:25

philipxy

14,867
6
39
83

answered Nov 10 '17 at 13:06

1

`based on the date` you need to explain your logic – Juan Carlos Oropeza Nov 10 '17 at 13:11

score 0 · Accepted Answer · edited May 06 '23 at 07:26

If you're looking to return the week that wasn't registered:

SELECT * 
FROM dbo.[week] w
WHERE NOT EXISTS (
  SELECT * FROM dbo.student_avaliation sa
  WHERE w.id = sa.idWeek)

SQLFiddle to prepare the data: http://sqlfiddle.com/#!6/c92c45/5

To know whether or not the week has anything registered (an entry in the student_avaliation table), left join and roll it up:

SELECT w.id
, w.start
, w.[end]
, w.name
, CASE WHEN sa.id is null then 'Not Registered' else 'Registered' end as WeekRegistered
FROM dbo.[week] w
LEFT JOIN dbo.student_avaliation sa
    ON w.id = sa.idWeek
GROUP BY w.id
, w.start
, w.[end]
, w.name
, CASE WHEN sa.id is null then 'Not Registered' else 'Registered' end
ORDER BY w.id

Note that the database dbo and using brackets [] is SQL-server only and not MySQL (for future readers)..sure you had to make some changes to the SQL code @LeonardoSantos — Raymond Nijland, Nov 10 '17 at 14:02

SQL: selecting data from a table that doesn't contain value from another table

2 Answers2