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!
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)) )
Try using XOR.
I think your basic logic is good but you need more parenthesis to group your first two IN clauses together.
try writing 2 queries & then 'union all' for output.
look for tag1 = $tag1 & tag1 <> $tag2
look for tag2 = $tag2 & tag2 <> $tag1
This is just logical representation, i have never used MySQL before.
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.
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)))
Try:
SELECT id FROM tagPairs WHERE (tag1 = '$tag1') XOR (tag2 = '$tag2')
