Left Join query not working

[geralf]

Hi,

I have a left join quert that's not working as it should

The query is like this:

SELECT tblProsesstider.CID, tblProsesstyper.PTID, tblProsesstyper.PBESKRIVELSE, tblProsesstider.CID
FROM tblProsesstyper LEFT JOIN tblProsesstider ON tblProsesstyper.PTID = tblProsesstider.PID;

The problem is that I don't get listed every record in tblProsesstyper unless the join fields has the same value, just as it was an inner join.

I want listed all records in tblProsesstyper even if it do not exist in tblProsesstider. This should have shown Null fields from the tblProsesstider

This should be simple to do but this is not working. Anyone that knows how to solve this?

Thanks in advance

 

[RoyVidar]

Just wondering, should you have used a RIGHT JOIN in stead?

 

[geralf]

Hi Roy-Vidar

I'm no expert on this but I've made a new left join query like this:

SELECT tblProsesstyper.PTID, tblProsesstyper.PBESKRIVELSE, tblProsesstider.CID, tblProsesstider.START, tblProsesstider.STOPP
FROM tblProsesstyper LEFT JOIN tblProsesstider ON tblProsesstyper.PTID = tblProsesstider.PID;

I want EVERY RECORD in tblProsesstyper displayed even if the join field has no match in tblProsesstider. Acts just as an inner join query. I changed it to try the right join but it made no difference.

Why don't I get every record in tblProsesstyper displayed in the output result?

Any ideas?

TIA

 

[RoyVidar]

I'm sorry, I'm not much of a query guy myself, but as far as I can see (he he - I had to test of course, I couldn't "see it") your query should return all the records from prosesstyper.

 

[geralf]

Thanks for your interest though. I have many left join queries in my project, so it's not that I've never used them before. This is strange. I've tried making this query directly on the tables so it should be straight forward. It's not essential that I make this work, because I now get listed the records that have a join match. It would be great to have all records present though from the tblProsesstyper. This query is used in a summary report, so it would be nice to have a fixed number od records in the report, displaying '0' where there are no match.

Why this don't work is very strange, and I won't get peace in my mind till I know why

Thanks for your reply Roy-Vidar.

 

[RoyVidar]

Strange.

Could you paste in some small samples of your data from both tables (of course garbled, if necessary) which illustrates the behaviour on your setup? Then someone could try to replicate the problem.

Have you tried copying both tables to a new database and create the same query on them?

 

[geralf]

Hi Roy-Vidar

Got it!

I use a criteria on the 'many table' (tblProsesstider) on the CID field which indicates each batch. So when the left join query runs - the criteria dont allow the null value CID record to be shown. Solved this by setting the criteria in a new query, and using this query in the left join query. Then it works!

I've attached a sample db for those who are interested.

Thanks for the help Roy-Vidar and good ideas.

PS The table tblProsesstider only has 1 batch but it doesn't have the process PID=7 (Driftsstans/Production Stoppage). This is now displayed as 0 minutes in the qsResultQuery.

Thanks a lot for the interest.