help with a sql query

Question

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!

Answer 1

Try using XOR.

Answer 2

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

Answer 3

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.

Answer 4

Try:

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

Answer 5

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.

Answer 6

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))  )

Answer 7

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)))