Select values that are not in another table

Question

Select values that are not in another table

Navigation

#1 by (2 votes)
#2 by (0 votes)

3

My scenario is as follows:

Row Table

IhavetheActivitytable

I need to mount a select, taking the values from the queue table, but I need to exclude from the queue the values that already exist in the activity table. If in the activity table there is already URL 976 and USER 96, then on select it would skip this line. Based on the image, the select of the queue would return only the IDs 975 and 973

I tried this, but did not:

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
)

Thank you in advance, thank you.

mysql sql

asked by anonymous 28.10.2017 / 15:28

2 answers

0

I believe this image will help you.

29.10.2017 / 15:41

Method that uses a random value as a parameter to change the characters of a string Returning linked data

score 2 · Accepted Answer

Assuming your structure and 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)

Output:

Seesolutionsworkingin SQLFiddle