Select two tables

Question

Good afternoon! Please I’m not managing to make a select

I have two tables in the bank (campaign and campanha_clicks)

I need to select only the results with the table type=2 (CAMPAIGN) and at the same time check if in the table campanha_clicks in the id_usuario field is not the user id

I want to show only the data of those who are not yet in the id_user field of the table campanha_clicks

SELECT DISTINCT 
  c.id,
  c.site,
  c.cliques_usados,
  c.cliques,
  c.localizacao,
  c.tipo,
  c.id_user
FROM
  campanha c
INNER JOIN campanha_cliques p
WHERE p.tipo = '2' 
  AND p.id_usuario != '$uiddw'

tried that but it doesn’t work

Answer 1

SELECT DISTINCT 
  c.id,
  c.site,
  c.cliques_usados,
  c.cliques,
  c.localizacao,
  c.tipo,
  c.id_user
FROM
  campanha c
INNER JOIN campanha_cliques p
ON c.id = p.id_campanha
WHERE p.tipo = '2' 
  AND p.id_usuario != '$uiddw'

I believe that only what is missing is the ON that is usually used to make the link between the tables involved in JOIN.

Answer 2

Your question is a little confused but maybe it is:

SELECT 
  c.id,
  c.site,
  c.cliques_usados,
  c.cliques,
  c.localizacao,
  c.tipo,
  c.id_user
FROM campanha c 
WHERE NOT EXISTS (SELECT * FROM campanha_cliques p WHERE p.tipo = '2' AND p.id_usuario == c.id_user);

or:

SELECT 
  c.id,
  c.site,
  c.cliques_usados,
  c.cliques,
  c.localizacao,
  c.tipo,
  c.id_user
FROM campanha c LEFT OUTER JOIN campanha_cliques p ON (p.id_usuario == c.id_user)
WHERE p.tipo = '2' AND p.id_usuario IS NULL;

Answer 3

Correct me if I’ve got it wrong:

SELECT DISTINCT 
  c.id,
  c.site,
  c.cliques_usados,
  c.cliques,
  c.localizacao,
  c.tipo,
  c.id_user
FROM
  campanha c
LEFT JOIN campanha_cliques p on p.id_campanha = p.campanha
WHERE p.tipo = '2' 
  AND c.id_user = '$uiddw'
  AND p.i_campanha is null

This select will return the table data campanha with tipo = 2, id_user = '$uiddw', where there are NO equivalent records in the campanha_cliques.

That’s it?