MySQL query, select from table, and return values not found

Question

I have a query:

SELECT DISTINCT phone
  FROM contacts
  WHERE (phone='123456' OR phone='456789' OR phone='789789')
     AND deleted=0 AND user=1

This will show me all rows that are in the contacts table, and they are not deleted. How can I return the values that are not found in the contacts table?

Note: There is no found field in my table. That's what i want to return
For example:

|   Phone   | deleted | found |
|-----------|---------|-------|
|  123456   |    0    |  YES  |
|  456789   |    1    |  YES  |
|  789789   |    0    |   NO  | <---

Thanks, Nicos

SELECT DISTINCT(phone) FROM contacts WHERE (phone = '123456' OR phone = '456789' OR phone = '789789') AND deleted = 0 AND found = 'NO' AND user = 1. You should use the "found" column too I guess. — Anwar
– Anwar, Commented Jan 27, 2015 at 11:04
there is no "found" column in my table, it was just to show, that the query was in the contacts Table, or not. '123456' AND '456789' are in my contacts table, '789789' is not, but i still want it returned in the query result — Nicos
– Nicos, Commented Jan 27, 2015 at 11:05
You want to get from the table some data that doesn't exist? — axiac
– axiac, Commented Jan 27, 2015 at 11:45
I want to get from the table, data that deleted=0, and data that are not included in the table. — Nicos
– Nicos, Commented Jan 27, 2015 at 11:50

stepozer · Accepted Answer · 2015-01-27 11:42:23Z

1

Simplest solution: check result on PHP and verify not exist phones.

EDIT:

If you want run it as one query you can create this statement:

SELECT t.phone
FROM
(
  SELECT 123456 as phone
  UNION ALL 
  SELECT 456789 as phone
  UNION ALL 
  SELECT 789789 as phone
) as t
LEFT JOIN  contacts as c
  ON t.phone = c.phone
WHERE c.phone IS NULL

But I'm not certain that it will work fast

edited Jan 27, 2015 at 11:42

answered Jan 27, 2015 at 11:07

stepozer

1,2111 gold badge11 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nicos Over a year ago

Stepozer, i thought i could find another solution, DB based, so i can save some PHP coding. So far, it is the only solution

axiac · Accepted Answer · 2015-01-27 11:50:37Z

1

Isn't it easier to do this in PHP?

// The list of phone numbers to search
$listAll = array('123456', '456789', '789789', );

// Use data from $listAll to compose this query:
$query = "
  SELECT DISTINCT phone
  FROM contacts
  WHERE phone IN ('123456', '456789', '789789')
     AND deleted=0 AND user=1
";

// Run the query, get the list of phone numbers in $listFound
// Let's suppose it produces:
//     $listFound = array('123456', '456789');

// Get the list of phone numbers that are missing from table
$listMissing = array_diff($listAll, $listFound);

answered Jan 27, 2015 at 11:50

axiac

73.4k11 gold badges120 silver badges150 bronze badges

Comments

Alex M. · Accepted Answer · 2015-01-27 11:07:42Z

0

Try this:

    select DISTINCT phone 
    from contacts 
    WHERE (phone='123456' OR phone='456789' OR phone='789789') 
    AND deleted = 0 
    AND user = 1
    AND found = 0

answered Jan 27, 2015 at 11:07

Alex M.

6412 gold badges6 silver badges20 bronze badges

4 Comments

stepozer Over a year ago

please read @Nicos answer there is no "found" column in my table,

Alex M. Over a year ago

So I don't know why he cannot achieve it.

stepozer Over a year ago

Because found is not column. Row 789789 NOT exist in table. Query must return NOT existed phones in table.

stepozer Over a year ago

See my answer to understand what it must to do

Kavita · Accepted Answer · 2015-01-27 12:35:09Z

0

Try this:

SELECT distinct phone 
FROM contacts 
WHERE phone NOT IN (123456,456789) AND
deleted = 0 AND 
user=1

edited Jan 27, 2015 at 12:35

answered Jan 27, 2015 at 11:58

Kavita

797 bronze badges

3 Comments

stepozer Over a year ago

please read @Nicos answer: there is no "found" column in my table,. So your answer is not correct. Row 789789 NOT exist in table. Query must return NOT existed phones in table.

stepozer Over a year ago

No:) For example your table contain phones (123456, 456789). And when you run SELECT with phones (123456, 456789, 789789) it must return phone 789789. Your query doesn't help with this:)

stepozer Over a year ago

See my answer to understand what it must to do

Collectives™ on Stack Overflow

MySQL query, select from table, and return values not found

4 Answers 4

1 Comment

Comments

4 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related