finding not equal data sets

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

Re: finding not equal data sets

On Jan 15, 9:25=A0am, kenny Ajram wrote:
> 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 =3D B.Y WHERE B.Y IS NULL UNION
SELECT Y FROM B LEFT JOIN A ON B.Y =3D 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 =3D B.ID WHERE B.ID IS NULL UNION
SELECT B.ID FROM B LEFT JOIN A ON B.ID =3D 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 =3D B.Y AND A.ID =3D
B.ID WHERE B.Y IS NULL UNION SELECT DISTINCTROW B.ID, Y FROM B LEFT
JOIN A ON B.Y =3D A.X AND A.ID =3D 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
CDMAPoster@FortuneJames.com