By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,863 Members | 838 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,863 IT Pros & Developers. It's quick & easy.

complex union query

P: n/a
Hi every body

I've a little problem with my query, I can't figure it out. I've three
tables:
Table A (EmployeNr, Date, Code)
Table B (EmployeNr, Date, Code) Which is an historic of Table A
Table C (BegD, EndD)

I can have all the records in table A that doesn't match in table B
but not from the other side.

Table A Table B Table C
0001 0001 20031001
20031002 20030930 20031031
PR1 PR1

0001 0001
20031003 20031001
PR1 Mal

0001
20031003
PR1
The result expected is:

0001
20031001
PR1

0001
20031002
PR1

I need to get all the records In table A which aren't in Table B
but also all the records in table B which aren't in table A but
matching with the period in table C.

Any suggestion will be greatly appreciated.
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Akinia wrote:
Hi every body

I've a little problem with my query, I can't figure it out. I've three
tables:
Table A (EmployeNr, Date, Code)
Table B (EmployeNr, Date, Code) Which is an historic of Table A
Table C (BegD, EndD)

I can have all the records in table A that doesn't match in table B
but not from the other side.

Table A Table B Table C
0001 0001 20031001
20031002 20030930 20031031
PR1 PR1

0001 0001
20031003 20031001
PR1 Mal

0001
20031003
PR1
The result expected is:

0001
20031001
PR1
Due to the difficulty of determining your example I wonder if PRI should
be Mal or PRI.


0001
20031002
PR1

I need to get all the records In table A which aren't in Table B
but also all the records in table B which aren't in table A but
matching with the period in table C.

Any suggestion will be greatly appreciated.


Create an unmatched query (Query/New/FindUnmatched) for TableA recs not in
TableB. Note your field names and their positions when you save the
query.

Now create an unmatched query (Query/New/FindUnmatched) for TableB recs
not in TableA. The field names, position and count should match those in
the first query. If this query does not contain a field contained in
Query1, you can always enter something like Expr1 : ""

Now modify this second query. Add TableC to it. Create a relationship
line between the date range fields of Table2 and Table3 if it does not
exist. While in desing mode of the second query, from the menu select
View/SQL. Highlight and copy the SQL to the clipboard.

Now open Query1 in design mode, select ViewSQL, remove the semi-colon at
the end, and enter
UNION ALL
and then paste the SQL statement from Query2.

Save and run.


Nov 12 '05 #2

P: n/a
Thanks for your answer U N ME
I have got the answer. see below:

SELECT A.*
FROM A LEFT JOIN B
ON A.EmployeNr=B.EmployeNr
AND A.Date = B.Date
AND A.Code = B.Code
Where B.EmployeeNr IS NULL

UNION ALL

Select B.*
FROM B INNER JOIN C
ON B.Period = C.Period
WHERE NOT EXITS
SELECT A.*
FROM A LEFT JOIN B
ON A.EmployeNr=B.EmployeNr
AND A.Date = B.Date
AND A.Code = B.Code

That's all.
Nov 12 '05 #3

P: n/a
Just a little mistake. here is the right SQL:

SELECT A.*
FROM A LEFT JOIN B
ON A.EmployeNr=B.EmployeNr
AND A.Date = B.Date
AND A.Code = B.Code
Where B.EmployeeNr IS NULL

UNION ALL

Select B.*
FROM B INNER JOIN C
ON B.Period = C.Period
WHERE NOT EXISTS
SELECT A.*
FROM A
WHERE A.EmployeNr=B.EmployeNr
AND A.Date = B.Date
AND A.Code = B.Code
Nov 12 '05 #4

P: n/a
Akinia wrote:
Just a little mistake. here is the right SQL:

SELECT A.*
FROM A LEFT JOIN B
ON A.EmployeNr=B.EmployeNr
AND A.Date = B.Date
AND A.Code = B.Code
Where B.EmployeeNr IS NULL

UNION ALL

Select B.*
FROM B INNER JOIN C
ON B.Period = C.Period
WHERE NOT EXISTS
SELECT A.*
FROM A
WHERE A.EmployeNr=B.EmployeNr
AND A.Date = B.Date
AND A.Code = B.Code


I see what you are doing now. I'm wondering if the Not Exists add some
time to the query. If it is not near instantaneous I might make the
first part Query1. Then in the second part make that Query2. I would
maybe make it

Select B.*
FROM B Left Join A On B.EmployeNR = A.EmployeeNR
B INNER JOIN C ON B.Period = C.Period
Where B.EmployeeNr IS NULL

Then create a third query where its
Select * from Query1 UNION ALL Select * from Query2

I think this would be much faster. Maybe not.

Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.