asked on

Select if it does not exist in another table

I have the following query on table fila

SELECT * 
FROM fila f
JOIN meta m
ON m.link = f.idlinks
WHERE m.meta2 < m.meta

Open in new window

It works, but I need something more ...
I need the query to get into the FILA table values that are no longer in the activity table, for example:
If the activity table already exists in the URL column and in the ACAO column, that data is disregarded and I get the next item in the row table

Example:
Table Fila

Table Atividade

As already exists in the activity table at URL 976 and USER 92, that record has to be disregarded and the query move to the next record in the FILA table

Pawan Kumar

I think you need this.

SELECT *
FROM fila f
JOIN meta m
ON m.link = f.idlinks
WHERE m.meta2 < m.meta
AND NOT EXISTS ( SELECT NULL FROM Atividade a WHERE a.url = f.idlinks )

Pawan Kumar

edited my last comment. pls refresh your page and try.

Pawan Kumar

Pls try this -

SELECT *
FROM fila f
JOIN meta m
ON m.link = f.idlinks
WHERE m.meta2 < m.meta
AND NOT EXISTS
( SELECT NULL FROM Atividade a WHERE a.url = f.idlinks AND a.usuario = p.usuario
AND a.url = 976 and a.usuario = 96
)

HainKurt

try

SELECT * 
  FROM fila f left JOIN meta m ON m.link = f.idlinks
 WHERE m.meta2 < m.meta and m.link is null

Open in new window

your screenshot does not match query here, column names are different, so I leave it as is...

PortletPaul

There are some parts of the question I don't understand, but assuming you want to EXCLUDE any rows from [fila] the do not match to rows of [Atividade] then you can use "left join excluding" i.e. left join to the table and then in the where clause exclude rows that do match.

SELECT
      f.*, m.*
FROM fila f
JOIN meta m ON m.link = f.idlinks
LEFT JOIN Atividade a ON f.idlinks = a.url
      AND p.usuario = a.usuario
WHERE m.meta2 < m.meta
AND a.url IS NULL

Open in new window

or one could use NOT EXISTS for a similar results

SELECT
      f.*, m.*
FROM fila f
JOIN meta m ON m.link = f.idlinks
WHERE m.meta2 < m.meta
AND NOT EXISTS (
      SELECT
            NULL
      FROM Atividade a
      WHERE f.idlinks = a.url
      AND p.usuario = a.usuario
)

Open in new window

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.