Select values that are not in another table

Asked

Viewed 566 times

3

My scenario is as follows:

Table Fila inserir a descrição da imagem aqui

I have the Activity table

inserir a descrição da imagem aqui

I need to mount a select, taking the values from the row table, however I need to exclude from the row the values that already exist in the activity table. If the activity table already exists the URL 976 and USER 96 , ai in select it would skip this line. Based on the image, the row select would return only Ids 975 and 973

I tried that way, but I couldn’t:

SELECT * 
FROM fila f
WHERE NOT EXISTS
(  
    SELECT NULL FROM atividade a 
    WHERE a.url = f.idlinks 
    AND a.usuario = f.usuario
    AND a.url = f.idlinks and a.usuario = 96
)

Thanks in advance, thank you.

mysql sql

2 answers

2


Assuming your structure and your data are something like:

CREATE TABLE fila
(
    id INTEGER,
    idlinks INTEGER,
    usuario INTEGER,
    url TEXT
);

CREATE TABLE atividade
(
    reg INTEGER,
    usuario INTEGER,
    acao INTEGER,
    datahora TIMESTAMP,
    url INTEGER
);

INSERT INTO fila ( id, idlinks, usuario, url ) VALUES ( 1, 976, 96, 'http://www.facebook.com/jesus/'  );
INSERT INTO fila ( id, idlinks, usuario, url ) VALUES ( 2, 975, 95, 'http://www.facebook.com/judas/'  );
INSERT INTO fila ( id, idlinks, usuario, url ) VALUES ( 3, 973, 93, 'http://www.facebook.com/maria/' );

INSERT INTO atividade ( reg, usuario, acao, datahora, url ) VALUES ( 3754, 96, 3, now(), 1011  );
INSERT INTO atividade ( reg, usuario, acao, datahora, url ) VALUES ( 3759, 96, 2, now(), 976  );
INSERT INTO atividade ( reg, usuario, acao, datahora, url ) VALUES ( 3760, 96, 1, now(), 988  );

Solution #1: LEFT JOIN

SELECT
    f.* 
FROM
     fila AS f
LEFT JOIN
    atividade AS a ON ( a.usuario = f.usuario AND a.url = f.idlinks )
WHERE
    a.reg IS NULL AND
    f.usuario = 93;

Solution #2: NOT IN

SELECT
    f.* 
FROM
    fila AS f
WHERE
    f.usuario = 93 AND
    (f.idlinks, f.usuario) NOT IN (SELECT a.url, a.usuario FROM atividade AS a)

Exit:

inserir a descrição da imagem aqui

See solutions working on Sqlfiddle

  • Thanks for the reply, but I need to specify which user I am looking for. If you are user 92, ai cannot appear in the queue if its links in case it has done interaction.

  • @Rene: In your question, you exemplify the user query of id = 96, according to its logic, as the record of this user is contained in both tables, none record should be returned. In your comment, you exemplify the user of id = 92, which is not contained in none tables, and following the logic of the question, none record should be returned. Here is an edit exemplifying a filter by the user id = 93, which is contained only in the table Fila.

0

I believe this image will help you.

Browser other questions tagged mysql sql

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