CODEWITHSUNDEEP
mysql
adrian4aes
adrian4aes

Reputation: 869

How to show records which doesnt exist in mysql?

If I do the following query:
select * from tabla where codigo in (33,34,55,10,22,50);
msyql shows me the records that match the query.

But what if I want to know what records from this query (33,34,55,10,22,50) which are not in the database?
Is there any way to know that without having to compare one record per one by myself?

Excuse me, I din't explain it well. This shows me the records which are in the database but don't match the query. I want to know what of this records (33,34,55,10,22,50) are not in the database.

Upvotes: 0

Views: 50

Answers (2)

echo_Me
echo_Me

Reputation: 37233

give this a try

set @suggest_value:=10;   // <---10 is the lowest value
select codigo,
@suggest_value:=@suggest_value+1,
if(codigo=@suggest_value, 0, 1) as missing_value
from tabla

Upvotes: 2

PinnyM
PinnyM

Reputation: 35531

Use NOT:

select * from tabla where codigo NOT in (33,34,55,10,22,50);

UPDATE

In light of this new explanation, do this:

SELECT n.id
FROM
  (SELECT 33 AS codigo
   UNION SELECT 34
   UNION SELECT 55 
   UNION SELECT 10
   UNION SELECT 22 
   UNION SELECT 50) AS n
LEFT JOIN tabla USING (codigo)
WHERE tabla.codigo IS NULL;

Unfortunately, MySQL (and many other systems) don't give a simple way to use an arbitrary list of numbers that aren't already in some table. If you need to do this dynamically, I would suggest adding these numbers to a temp table in place of a hard-coded batch of UNIONs. Or generate a reusable numbers table (I find this fairly useful), and use the values from there. For example:

SELECT nt.id
FROM numbers_table nt
LEFT JOIN tabla ON nt.id = tabla.codigo
WHERE nt.id IN (33,34,55,10,22,50)
  AND tabla.codigo IS NULL

Upvotes: 2

Related Questions