MySQL doesn't work that way...
(hence my confusion - sorry ita!)
I have really old Access skills ...
This is so nice. Having everyone together for my birthday. Of course, you could smash in all my toes with a hammer and it will still be the bestest Buffy Birthday Bash in a big long while.
Buffy ,'Potential'
Buffistechnology 3: "Press Some Buttons, See What Happens."
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!
hippocampus - May 04, 2007 8:38:14 am PDT #1483 of 25496
not your mom's socks.
Gudanov - May 04, 2007 8:43:11 am PDT #1484 of 25496
Coding and Sleeping
SELECT colA, colB, * FROM TableA WHERE ISNULL(colA=colB)
§ ita § - May 04, 2007 8:44:13 am PDT #1485 of 25496
Well not canonically, no, but this is transformative fiction.
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...)
Gudanov - May 04, 2007 8:44:59 am PDT #1486 of 25496
Coding and Sleeping
The Query Designer will get it wrong.
Jon B. - May 04, 2007 8:47:54 am PDT #1487 of 25496
A turkey in every toilet -- only in America!
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.
Barkley - May 04, 2007 8:48:54 am PDT #1488 of 25496
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')
Gudanov - May 04, 2007 8:49:19 am PDT #1489 of 25496
Coding and Sleeping
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.
§ ita § - May 04, 2007 8:50:14 am PDT #1490 of 25496
Well not canonically, no, but this is transformative fiction.
Gud, that's the answer. I dislike that it's the answer, since it means I need a more complex WHERE clause to catch nulls and normal inequality. Does "=" have three possible values in most RDBMSs?
::searches::
Looks like MySQL behaves the same way, and includes a "NULL safe equal" to accomodate it.
I had no idea.
Gudanov - May 04, 2007 8:53:37 am PDT #1491 of 25496
Coding and Sleeping
Gud, that's the answer. I dislike that it's the answer, since it means I need a more complex WHERE clause to catch nulls and normal inequality.
Sometimes the truth hurts.
Liese S. - May 04, 2007 8:55:15 am PDT #1492 of 25496
"Faded like the lilac, he thought."
Yeah, I don't think there's any way around a more complex where. This is cludgy, but...
SELECT TableA.colA, TableA.colB
FROM TableA
WHERE [colA]<>[colB]
OR (IsNull([colA])=True AND IsNull([colB])=False)
OR (IsNull([colB])=True AND IsNull([colA])=False);
You don't need the last bit since your dataset doesn't contain nulls in B, but you would if it might.