Help for an complex querry

Scotty

New member
Local time
Today, 08:27
Joined
Sep 18, 2011
Messages
4
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 :confused:

Scotty
 
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.
 
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
 
:confused:
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
 
If I understand the question correctly, this is the SQL you are looking for.

Code:
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
 
If I understand the question correctly, this is the SQL you are looking for.

Code:
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
 

Users who are viewing this thread

Back
Top Bottom