Union query and exclusion of data

[Syed Zeeshan Haider]

Hello Everybody,
I am usin Access 2003 Pro and trying to create a rather complex union query
but don't seem to implement conditions.

Let's say I have two tables called A and B. A has column called x and B also
has a column called x. I am trying to join these two columns into one column
with following conditions:
If e is a member of both A and B then the query should include e from table
A only (because the second column calculates a value which depends on the
choice of table for picking e). I have tried all INNER JOIN, LEFT JOIN and
RIGHT JOIN but none of them have worked so far.

Here is an example of what I did:

select [A].[x]
FROM [A] INNER JOIN ON [A].[x] = .[x]
where (([A].[x]) Is Not Null) and (([A].[x])<>(.[x]))
UNION select .[x]
from ;

Any ideas what am I doing wrong here?
Any help will be highly appreciated.

Thank you,

 

[Gary Walter]

"Syed Zeeshan Haider"wrot:

I am usin Access 2003 Pro and trying to create a rather complex union query
but don't seem to implement conditions.

Let's say I have two tables called A and B. A has column called x and B also
has a column called x. I am trying to join these two columns into one column
with following conditions:
If e is a member of both A and B then the query should include e from table
A only (because the second column calculates a value which depends on the
choice of table for picking e). I have tried all INNER JOIN, LEFT JOIN and
RIGHT JOIN but none of them have worked so far.

Here is an example of what I did:

select [A].[x]
FROM [A] INNER JOIN ON [A].[x] = .[x]
where (([A].[x]) Is Not Null) and (([A].[x])<>(.[x]))
UNION select .[x]
from ;

Any ideas what am I doing wrong here?
Any help will be highly appreciated.

Thank you,

Hi Syed,

It sure sounds like you want
(just threw in "FromTable" to verify)

SELECT
"A" As FromTable,
A.x As WantValue
FROM A
UNION
SELECT
"B",
B.x
FROM
B LEFT JOIN A
ON
B.x = A.x
WHERE
A.x IS NULL
ORDER BY
WantValue,
FromTable;

the first select gets all A.x...

the last select gets all of B
except where value of B.x in A.x...

good luck,

gary

 

[Syed Zeeshan Haider]

Thank you for your response. This query included data only from B when it
was shared by both tables. I am trying to exclude and include A in the given
case.

Thanks again for your time!

"Syed Zeeshan Haider"wrot:

I am usin Access 2003 Pro and trying to create a rather complex union query
but don't seem to implement conditions.

Let's say I have two tables called A and B. A has column called x and B also
has a column called x. I am trying to join these two columns into one column
with following conditions:
If e is a member of both A and B then the query should include e from table
A only (because the second column calculates a value which depends on the
choice of table for picking e). I have tried all INNER JOIN, LEFT JOIN
and
RIGHT JOIN but none of them have worked so far.

Here is an example of what I did:

select [A].[x]
FROM [A] INNER JOIN ON [A].[x] = .[x]
where (([A].[x]) Is Not Null) and (([A].[x])<>(.[x]))
UNION select .[x]
from ;

Any ideas what am I doing wrong here?
Any help will be highly appreciated.

Thank you,

Hi Syed,

It sure sounds like you want
(just threw in "FromTable" to verify)

SELECT
"A" As FromTable,
A.x As WantValue
FROM A
UNION
SELECT
"B",
B.x
FROM
B LEFT JOIN A
ON
B.x = A.x
WHERE
A.x IS NULL
ORDER BY
WantValue,
FromTable;

the first select gets all A.x...

the last select gets all of B
except where value of B.x in A.x...

good luck,

gary

 

[John Spencer]

Get records that are in both A and B
SELECT A.X, "A" as Source
FROM A INNER JOIN B
ON A.X = B.X

Get Records in A that are not in B
SELECT A.X, "A" as Source
FROM A LEFT JOIN B
ON A.X = B.X
WHERE B.X is Null

Get Records in B that are not in A
SELECT B.X, "B" as Source
FROM A RIGHT JOIN B
ON A.X = B.X
WHERE A.X is Null

You should be able to use a union query to get
-- Records in A or in B but not in both (Last two queries above)
-- Records in A or B (all three queries)
-- Records in A (First two queries - although just a simple select of
records in A will do this more efficiently)
-- Records in A and B or in B (First and third - again a simple select
of records in B will do this more efficiently)


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Gary Walter]

Hi Syed,

I am sure you are explaining in mathematically accurate
terms, but I regretably must be in some fog today...

Is there a chance you could give some example data
for A.x and B.x, then result from that data that you want
including which table final value comes from?

sorry...my old reliable computer went down
Friday and am using old noisy (jet-engine) server
while I try to revive old faithful -- so noise and frustrations
are getting in way of understanding...(I'm sure its not
old brain..hah)

gary

Thank you for your response. This query included data only from B when it
was shared by both tables. I am trying to exclude and include A in the given
case.

Thanks again for your time!

"Syed Zeeshan Haider"wrot:

I am usin Access 2003 Pro and trying to create a rather complex union query
but don't seem to implement conditions.

Let's say I have two tables called A and B. A has column called x and B also
has a column called x. I am trying to join these two columns into one column
with following conditions:
If e is a member of both A and B then the query should include e from table
A only (because the second column calculates a value which depends on the
choice of table for picking e). I have tried all INNER JOIN, LEFT JOIN
and
RIGHT JOIN but none of them have worked so far.

Here is an example of what I did:

select [A].[x]
FROM [A] INNER JOIN ON [A].[x] = .[x]
where (([A].[x]) Is Not Null) and (([A].[x])<>(.[x]))
UNION select .[x]
from ;

Any ideas what am I doing wrong here?
Any help will be highly appreciated.

Thank you,

Hi Syed,

It sure sounds like you want
(just threw in "FromTable" to verify)

SELECT
"A" As FromTable,
A.x As WantValue
FROM A
UNION
SELECT
"B",
B.x
FROM
B LEFT JOIN A
ON
B.x = A.x
WHERE
A.x IS NULL
ORDER BY
WantValue,
FromTable;

the first select gets all A.x...

the last select gets all of B
except where value of B.x in A.x...

good luck,

gary