Got a question about technology? Ask it here. Discussion of hardware, software, TiVos, multi-region DVDs, Windows, Macs, LINUX, hand-helds, iPods, anything tech related. Better than any helpdesk!
Null values would screw up SELECT colA, colB, * FROM TableA WHERE colA=colB too though, no?
Doesn't look like there are any values in colB that are null. Now, I did try switching the order in the expression of colA and colB (frustrated and bored) and that didn't fix it either.
Grr.
Funnily enough, a coworker told me that colA==colB, and I knew it wasn't because I'm working on reconciling data. So when the query agreed with him, I got paranoid.
When I couldn't flip the one query and get the correct results, I began to wonder if he was using Jedi mind powers or something to mess with me.
::returns from quick test::
Ayup. NULL isn't equal to NULL either. And having NULL in either colA or colB is making it night impossible to return that row with a criterion that compares the two.
ita - have you tried using design view to build a simple query? Your sql should work, but using design view might help.
MySQL doesn't work that way...
(hence my confusion - sorry ita!)
I have really old Access skills ...
SELECT colA, colB, * FROM TableA WHERE ISNULL(colA=colB)
have you tried using design view to build a simple query? Your sql should work, but using design view might help.
I started it in Design view, and then switched to SQL view to enter my specifics. Then, when it was weird, switched back to Design view where everything looked theoretically normal. I'm not sure of any other way to enter it into Design view than like:
Field:TableA.*colAcolB
Table:TableATableATableA
Sort:
Show:YYY
Criteria:<>[colB]
(and it'll be a miracle if that table comes out right...)
The Query Designer will get it wrong.
That design
should
work, but you might also try creating a fourth field that's an expression, like expr1: [colA] = [colB], and then have the criteria in the field be equal to false.
If you have nulls in either column, this is the query in Oracle:
select colA, colB, r.* from table r
where nvl(colA,'NOTMATCHA') != nvl(colB,'NOTMATCHB')
I think mysql's version of the nvl function is ifnull, but I don't have mysql here to test:
select colA, colB, * from table
where ifnull(colA,'NOTMATCHA') != ifnull(colB,'NOTMATCHB')
expr1: [colA] = [colB], and then have the criteria in the field be equal to false.
It will evaluate to NULL instead of false because Access is whacked out.
