Help for an complex querry

[Scotty]

I have 2 tables:

tblPersoneel: with standard LijnNr
PersID PersNaam LijnNr
1 Werknemer01 | 1
2 Werknemer02 | 1
3 Werknemer03 | 2
4 Werknemer04 | 2
5 Werknemer05 | 3
6 Werknemer06 | 3
7 Werknemer07 | 3
8 Werknemer08 | 4
9 Werknemer09 | 4

tblPlanningLijn With changed LijnNr on particulary date.
ProdDatum PersId LijnNr
18/09/2011 3 | 4
19/09/2011 3 | 1

I want next data from an query:
Query7If PersID has no date in tblPlanning
query must show a view of all PersID and PersNaam from tblPersoneel.

If by example selection on date 18/09/2011
query must show all PersId and PersNaam on that date from table tblPlanninglijn and + the other PersID and PersNaam from tblPersoneel.

so to have the correct lijnNr on date of 18/09/2011 and standard lijnnr from the other PersNaam from table tblPersoneel.

The results must always be 9 records with the richt LijnNr.

Many thanks in advance

Scotty

 

[Galaxiom]

You need an outer join to show all the records from one table.
Right click on the join and set the join type.

Welcome to the forum.

 

[Scotty]

Hi,
Thanks for your answer, but outer join does not give the wright solution

If selected date = 18/09/2011

The result must be:

PersID PersNaam LijnNr
1 Werknemer01 | 1
2 Werknemer02 | 1
3 Werknemer03 | 2 > 18/09/2011 > 3 Werknemer03 | 4
4 Werknemer04 | 2
5 Werknemer05 | 3
6 Werknemer06 | 3
7 Werknemer07 | 3
8 Werknemer08 | 4
9 Werknemer09 | 4

if no date corresponding like 17/09/2011
The result must be:

PersID PersNaam LijnNr
1 Werknemer01 | 1
2 Werknemer02 | 1
3 Werknemer03 | 2
4 Werknemer04 | 2
5 Werknemer05 | 3
6 Werknemer06 | 3
7 Werknemer07 | 3
8 Werknemer08 | 4
9 Werknemer09 | 4

 

[Scotty]

Hi,
Thanks for your answer, but outer join does not give the wright solution

If selected date = 18/09/2011

The result must be:

PersID PersNaam LijnNr
1 Werknemer01 | 1
2 Werknemer02 | 1
3 Werknemer03 | 2 > 18/09/2011 > 3 Werknemer03 | 4
4 Werknemer04 | 2
5 Werknemer05 | 3
6 Werknemer06 | 3
7 Werknemer07 | 3
8 Werknemer08 | 4
9 Werknemer09 | 4

if no date corresponding like 17/09/2011
The result must be:

PersID PersNaam LijnNr
1 Werknemer01 | 1
2 Werknemer02 | 1
3 Werknemer03 | 2
4 Werknemer04 | 2
5 Werknemer05 | 3
6 Werknemer06 | 3
7 Werknemer07 | 3
8 Werknemer08 | 4
9 Werknemer09 | 4

 

[Galaxiom]

If I understand the question correctly, this is the SQL you are looking for.

SELECT A.PersID, A.PersNaam, IIF(B.LijnNr Is Null, A.LijnNr, B.LijnNr)
FROM tblPersoneel AS A
LEFT OUTER JOIN 
         (
          SELECT PersID, LijnNr
          FROM tblPlanningLijn
          WHERE ProdDatum = [Enter Selected Date]
         ) AS B
ON A.PersID = B.PersID
ORDER BY A.PersID

 

[Scotty]

If I understand the question correctly, this is the SQL you are looking for.

SELECT A.PersID, A.PersNaam, IIF(B.LijnNr Is Null, A.LijnNr, B.LijnNr)
FROM tblPersoneel AS A
LEFT OUTER JOIN 
         (
          SELECT PersID, LijnNr
          FROM tblPlanningLijn
          WHERE ProdDatum = [Enter Selected Date]
         ) AS B
ON A.PersID = B.PersID
ORDER BY A.PersID

Thanks... is working Perfect