CODEWITHSUNDEEP
mysql
ajsie
ajsie

Reputation: 79686

help with a sql query

i need help with one more sql query.

if i've got 2 columns in a table:

tag1 tag2

and want to select rows that got either $tag1 OR $tag2 but never $tag1 AND $tag2, how could i write the sql query?

i've tried with:

    SELECT id
    FROM tagPairs
    WHERE (tag1 IN ($tag1, $tag2))
    OR (tag2 IN ($tag1, $tag2))
    AND ((tag1 != $tag1 OR tag1Id != $tag2) AND (tag2 != $tag1 OR tag2 != $tag2))

but it doesnt seem to work.

would appreciate some help here, thanks!

Upvotes: 0

Views: 98

Answers (7)

Tyler Smith
Tyler Smith

Reputation: 727

Try:

SELECT id FROM tagPairs WHERE (tag1 = '$tag1') XOR (tag2 = '$tag2')

Upvotes: 0

PhiwassailerKing
PhiwassailerKing

Reputation: 46

I think that this:

SELECT id FROM tagPairs WHERE tag1 XOR tag2

only checks that one of the fields is not NULL if it even does that.

Clarification Question:

Are you saying that either value ($tag1 or $tag2) could be in either field (tag1 or tag2)? Can either of the fields have other values besides $tag1, $tag2, NULL?

If the answer to both questions is yes then I agree that you are doing a basic XOR. If you can't figure out how to use XOR here, then the logical equivalent of "a xor b" is 

( a and not b ) or 
( b and not a )

In your case 

a = (tag1 in ($tag1,$tag2))
b = (tag2 in ($tag1,$tag2))

not a = (tag1 not in ($tag1,$tag2))
not b = (tag2 not in ($tag1,$tag2))

So then just substitute and you get:

(  (tag1 in ($tag1,$tag2)) and (tag2 not in ($tag1,$tag2))  ) or 
(  (tag2 in ($tag1,$tag2)) and (tag1 not in ($tag1,$tag2))  )

Upvotes: 2

lins314159
lins314159

Reputation: 2520

Beware of nulls.

WHERE (tag1 IN ($tag1, $tag2)
      OR tag2 IN ($tag1, $tag2))
  AND (tag1 IS NULL
      OR tag2 IS NULL
      OR NOT(tag1 IN ($tag1, $tag2) AND tag2 IN ($tag1, $tag2)))

Upvotes: 0

Alex Shirshov
Alex Shirshov

Reputation: 655

WHERE tag1 IN ($tag1, $tag2)
OR tag2 IN ($tag1, $tag2)
AND NOT (tag1 IN ($tag1, $tag2) AND tag2 IN ($tag1, $tag2))

This is pure speculation, I never used MySQL. Is there NOT operator? I hope there is.

Upvotes: 0

SoftwareGeek
SoftwareGeek

Reputation: 15772

try writing 2 queries & then 'union all' for output.

  1. look for tag1 = $tag1 & tag1 <> $tag2

  2. look for tag2 = $tag2 & tag2 <> $tag1

This is just logical representation, i have never used MySQL before.

Upvotes: 0

Devin Ceartas
Devin Ceartas

Reputation: 4829

I think your basic logic is good but you need more parenthesis to group your first two IN clauses together.

Upvotes: 0

Daniel A. White
Daniel A. White

Reputation: 190943

Try using XOR.

Upvotes: 0

Related Questions