Display record when another table does not exist reference

1

I need only display some record if the id field of the noticias table is not referenced in the idnoticia field of the fotos_noticias table.

SELECT
    noticias.id,
    noticias.titulo,
    noticias.slug,
    fotos_noticias.foto
FROM noticias
INNER JOIN fotos_noticias
    ON noticias.id <> fotos_noticias.idnoticia
WHERE
GROUP BY noticias.id

Unfortunately this SQL above displays all records without restriction

    
asked by anonymous 25.09.2018 / 20:11

3 answers

3

Analyzing the following image:

You want to make a LEFT JOIN (the chart on the left, the bottom chart) by eliminating the results where the tables intersect. To do this, just use WHERE tabela_b.id IS NULL .

In your example it would look like this:

SELECT
    noticias.id,
    noticias.titulo,
    noticias.slug,
    fotos_noticias.foto
FROM noticias
LEFT JOIN fotos_noticias
    ON noticias.id = fotos_noticias.idnoticia
WHERE fotos_noticias.idnoticia IS NULL
GROUP BY noticias.id
    
25.09.2018 / 20:38
4

Gladison, Let's see if I get it:

Do you want all records in the news table that do not exist in news_photo? If yes, the select would be this:

SELECT noticias.id, noticias.titulo, noticias.slug, fotos_noticias.foto
  FROM noticias   
 WHERE noticias.id not in ( select idnoticia from fotos_noticias)
 GROUP BY noticias.id
    
25.09.2018 / 20:22
0

In Set Theory what you are looking for is C = A - B where A is the news and B is the photo.

There are several ways to solve what you need. Here are some using T-SQL.

-- código #1
SELECT id from noticias
except
SELECT idnoticia from fotos_noticias;

or

-- código #2
SELECT N.id 
  from noticias as N
       left join fotos_noticias as FN on FN.idnoticia = N.id
  where FN.idnoticia is null;

or

-- código #3
SELECT N.id
  from noticias as N
  where not exists (SELECT * 
                      from fotos_noticias as FN
                      where FN.idnoticia = N.id);

or

-- código #4
SELECT N.id
  from noticias as N
  where not in (SELECT FN.idnoticia
                      from fotos_noticias as FN);

I did not test; there may be error (s).

    
25.09.2018 / 20:44