CODEWITHSUNDEEP
phpmysqlconditional-statements
niko
niko

Reputation: 9393

Into a single query

I have got a table

 unam     fnam

 john     alex
 alex     john

I want a query that returns true if unam 'john' == fnam 'alex' or unam 'alex' == fname 'john'

I dont know how to do it in a single query . My code 

$re = mysql_query("SELECT 1 FROM tab WHERE unam='john' and fnam='alex' LIMIT 1"); 
$ir = mysql_query("SELECT 1 FROM tab WHERE unam='alex' and fnam='john' LIMIT 1"); 

if ((!(mysql_num_rows($re))) && (!(mysql_num_rows($ir)))) echo 'not exists';

I have executed 2 seperate queries (one for unam 'john' == fnam 'alex' and other for unam 'alex' == fname 'john', if both the queries do not have any rows it echos not exists.

I was thinking may be it can be optimized to a single query.

Upvotes: 1

Views: 81

Answers (5)

fthiella
fthiella

Reputation: 49049

Just a different approach, using EXISTS, and a different syntax:

SELECT
  EXISTS (SELECT NULL FROM names
          WHERE (unam, fnam) IN (('john', 'alex'),('alex','john')))

Please see fiddle here.

Upvotes: 1

Sumoanand
Sumoanand

Reputation: 8929

You can use OR statement to achieve this.

Try this:

mysql_query("SELECT 1 
FROM tab 
WHERE 
(unam='john' and fnam='alex') 
OR 
(unam='alex' and fnam='john')
limit 1");

Upvotes: 2

steven
steven

Reputation: 4875

$re = mysql_query("SELECT COUNT(*) FROM tab WHERE (unam='john' and fnam='alex') OR (unam='alex' and fnam='john')");

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269543

You can put your logic in the where clause like this:

select 1
from tab
where (uname = 'john' and fname = 'alex') or
      (fname = 'alex' and uname = 'john')
limit 1

By the way, if you want to always return a value, say 0 or 1, you can use aggregation:

select coalesce(max(1), 0) as retval
from tab
where (uname = 'john' and fname = 'alex') or
      (fname = 'alex' and uname = 'john')

If no rows are selected, then the max() returns NULL which the coalesce() turns into 0.

Upvotes: 2

chrislondon
chrislondon

Reputation: 12031

You can do an OR conditional to check for multiple different conditions.

SELECT 1 
FROM tab 
WHERE (unam='john' and fnam='alex') 
    OR (fnam='john' and unam='alex') 
LIMIT 1

Upvotes: 5

Related Questions