Pbm with a complex query

[akinia]

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 20031001 20031031
PR1 PR1

0001 0001
20031003 20031002
PR1 Mal

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 but matching with the period in table C.

Any suggestion will be greatly appreciated.

 

[Golom]

I'm assuming that all three fields that you have listed in Tables "A" and "B" are the primary key for those tables. To get the records in table "A" that are not in "B"

   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

then, all the records in "B" that match the period in "C"

   Select B.*
   FROM   B INNER JOIN C
          ON B.Period = C.Period

and to get both sets of records together, use a UNION

   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

"UNION ALL" returns all records even if there are duplicates. "UNION" (without the "ALL&quot would eliminate duplicate records.

 

[akinia]

Thanks Golom for your fast answer. I try it and let you know

 

[akinia]

I've forgotten something important.
I also don't want to see the records in table B which are in table A.

Thanks again for the answers.

 

[Golom]

OK ... then change the second SELECT in the UNION to

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

This just eliminates records when those records exist in table A.

 

[akinia]

Hi Golom
You are fantastic you work also on saturday.
It works really good; I've try many options without success but now I'm happy and I've learned something.
Thanks a lot Golom

Have a nice Sunday.

Akinia