finding not equal data sets

[kenny Ajram]

hi everybody,

i have a rather short and possibly easy question: i have two tables
and want to find all unequal datasets. for instance like this: i have
a table A holding fields id and x and a table B holding id and y. now
i want to show a table C holding all ids except the intersection of
both.

I tried something like

SELECT A.id, B.id
from A, B
WHERE A.id<>B.id;

it didnt let me use "group by", I didnt quite get why.

Please help, also contributions without sql but something over the gui
(drag and drop) are welcome!

thank you.

yours, Kenny Ajram

[CDMAPoster]

On Jan 15, 9:25*am, kenny Ajram <[e-mail]:

hi everybody,

i have a rather short and possibly easy question: i have two tables
and want to find all unequal datasets. for instance like this: i have
a table A holding fields id and x and a table B holding id and y. now
i want to show a table C holding all ids except the intersection of
both.

I tried something like

SELECT A.id, B.id
from A, B
WHERE A.id<>B.id;

it didnt let me use "group by", I didnt quite get why.

Please help, also contributions without sql but something over the gui
(drag and drop) are welcome!

thank you.

yours, Kenny Ajram

If you only care about x and y values themselves and not about the
(id, x), (id, y) combinations:

For table A with X and table B with Y,

qryXOR:
SELECT X FROM A LEFT JOIN B ON A.X = B.Y WHERE B.Y IS NULL UNION
SELECT Y FROM B LEFT JOIN A ON B.Y = A.X WHERE A.X IS NULL;

It's a UNION of two unmatched queries.

A
ID AutoNumber
X Dbl
ID X
1 1
2 2
3 3
4 4
5 5
6 6
7 7

B
ID AutoNumber
Y Dbl
ID Y
1 4
2 5
3 6
4 7
5 8
6 9

!qryXOR:
X
1
2
3
8
9

If you only care about the ID values and not the (id, X), (id, Y)
combinations:

SELECT A.ID FROM A LEFT JOIN B ON A.ID = B.ID WHERE B.ID IS NULL UNION
SELECT B.ID FROM B LEFT JOIN A ON B.ID = A.ID WHERE A.ID IS NULL;

yields

ID
7

If you care about the (id, x), (id, y) combinations, perhaps use:

SELECT DISTINCTROW A.ID, X FROM A LEFT JOIN B ON A.X = B.Y AND A.ID =
B.ID WHERE B.Y IS NULL UNION SELECT DISTINCTROW B.ID, Y FROM B LEFT
JOIN A ON B.Y = A.X AND A.ID = B.ID WHERE A.X IS NULL;

After changing the first three Y values in B to 1, 2 and 3, I get:

ID X
4 4
4 7
5 5
5 8
6 6
6 9
7 7

Each unmatched query can use GROUP BY. Please include an example of
the grouping you want if you need more help.

James A. Fortune
CD********@FortuneJames.com