Display record when another table has no reference

Asked

Viewed 187 times

1

I need you to just display some record if the field id table noticias has no reference in the field idnoticia table fotos_noticias.

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

sql

3 answers

4

Gladison, let me get this straight:

Do you want all the records of the table news that do not exist in fotos_noticias? 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

3


Analyzing the following image:

SQL Joins Cheatsheet

You want to make a LEFT JOIN (the graph to the left, the bottom) 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

0

In set theory what you’re looking for is C = A - B where A is the news and B is the photos.

There are several ways to solve what you need. Here are a few ways to solve it 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 have not tested; error(m) may exist(s).

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.