Is it possible to left join a sql select statement with conditions with a table? or do I have to cre-CodePudding

So I have a Invoice table where I created the following query, let's call it "IntInvoice"

SELECT *
FROM [U_Concatenar_Invoice Control]
WHERE [U_Concatenar_Invoice Control].[Décision prise] is null
and
[U_Concatenar_Invoice Control].Concatenar is not null
and
[U_Concatenar_Invoice Control].N_AVD is not null;

next I created another query to left join a different table with the "IntInvoice" query

SELECT IntInvoice.*
FROM IntInvoice
LEFT JOIN Int_Account 
ON IntInvoice.Concatenar=Int_Account.Concatenar
WHERE Int_Account.Concatenar Is Null and IntInvoice.Concatenar is not null;

it works fine as it is, but is it possible to have it all in the same query to avoid having to create a lot of them when I have to work with the data?

CodePudding user response：

Clarifying my comment, I would think you can combine these two into a query:

SELECT [U_Concatenar_Invoice Control].*
FROM
  [U_Concatenar_Invoice Control]
  LEFT JOIN Int_Account 
    ON [U_Concatenar_Invoice Control].Concatenar=Int_Account.Concatenar
WHERE
  [U_Concatenar_Invoice Control].[Décision prise] is null and
  [U_Concatenar_Invoice Control].Concatenar is not null and
  [U_Concatenar_Invoice Control].N_AVD is not null and
  Int_Account.Concatenar Is Null and
  -- this is duplicated from above so should not be necessary:
  [U_Concatenar_Invoice Control].Concatenar is not null

There are a few cases where this would not be possible (or easy), but this was a relatively simple query to integrate.

Examples would be if two queries both did aggregate/group by and you wanted to join the results.

-- EDIT 4/20/2022 --

Per your latest comment, where Int_Account is actually the following:

SELECT * FROM U_Concatenar_AccountManagement WHERE 
U_Concatenar_AccountManagement.Status_AVD="À traiter";

You could rewrite the query above as follows:

SELECT [U_Concatenar_Invoice Control].*
FROM
  [U_Concatenar_Invoice Control]
  LEFT JOIN [U_Concatenar_AccountManagement] ON
    [U_Concatenar_Invoice Control].Concatenar=Int_Account.Concatenar and
    [U_Concatenar_AccountManagement].Status_AVD="À traiter"
WHERE
  [U_Concatenar_Invoice Control].[Décision prise] is null and
  [U_Concatenar_Invoice Control].Concatenar is not null and
  [U_Concatenar_Invoice Control].N_AVD is not null and
  [U_Concatenar_AccountManagement].Concatenar Is Null -- fixed
   --  [Int_Account.Concatenar] Is Null  -- replaced this

My guess (and this is just a guess) is that you added:

[U_Concatenar_AccountManagement].Status_AVD="À traiter"

To the "where" clause instead of as part of the join. I don't know Access very well, but in standard SQL, these mean two very different things:

from x                               from x
  left join y on x.id = y.id           left join y on x.id = y.id
     and y.z = 'Foo'                 where y.z = 'Foo'

The one on the left preserves the left join and only left joins on records where the condition is true. The one on the right effectively converts your left join to an inner join, which based on the rest of your query seems like it would return zero results (you only want records where you did not find a match in y).

Give that a try and see if it works. Hopefully you can follow this pattern and eliminate the need for so many subqueries (queries of queries). In the long run these are so much harder to maintain because you have to follow the trail of breadcrumbs back every time.

-- EDIT 4/21/2022 --

I know you've put this to bed, but I had to know... it turns out as you discovered, Access does not support where conditions in the join. I find this crazy. You can, however, accomplish the same goal by creating an inline subquery and putting your condition there.

Here is what worked for me:

SELECT [U_Concatenar_Invoice Control].*
FROM [U_Concatenar_Invoice Control]
LEFT JOIN (select * from U_Concatenar_AccountManagement where Status_AVD="À traiter") AS X
  ON [U_Concatenar_Invoice Control].Concatenar = X.Concatenar
WHERE
  [U_Concatenar_Invoice Control].[Décision prise] Is Null AND 
  [U_Concatenar_Invoice Control].Concatenar Is Not Null AND
  [U_Concatenar_Invoice Control].N_AVD Is Not Null AND
  X.Concatenar Is Null;

In my opinion, this is no better than the approach you are using to have two queries, one that calls the other. What you gain in conciseness you lose in transparency. Pick one or the other; neither is ideal.