Yes no problem,
declare @JobNo_ as nvarchar(max)
set @JobNo_ = 'P0002333'
SELECT
[COMPANY$Purchase Line].[Document No_] AS [COMPANY$Purchase Line Document No_] --- Inkoop order lijn, document nummer
,[COMPANY$Purchase Line].Quantity AS [COMPANY$Purchase Line Quantity] --- Inkoop order lijn, aantal
,[COMPANY$Purchase Line].[Unit Cost (LCY)] --- Inkoop order lijn , prijs per stuk
,[COMPANY$Purchase Line].[Job No_] --- Inkoop order lijn, projectnummer
,[COMPANY$Purchase Line].[Quantity Invoiced] --- Inkoop order lijn, gefactureerd aantal
,[COMPANY$Purchase Line].[Blanket Order No_] --- Inkoop order lijn, Externe referentie ???
,[COMPANY$Purchase Line].[Budget Account Code] --- Inkoop order lijn, account code NVT
,[COMPANY$Purchase Line].[Subcontract_ Contract No_] --- Inkoop order lijn, subcontract nr NVT
,[COMPANY$Purch_ Rcpt_ Line].[Document No_] AS [COMPANY$Purch_ Rcpt_ Line Document No_] --- Inkoop ontvanst lijn, document nr
,[COMPANY$Purch_ Rcpt_ Line].[Line No_] --- Inkoop ontvangst lijn, lijn nr
,[COMPANY$Purchase Line].[Buy-from Vendor No_] --- Inkoop order lijn, verkoper nr
,[COMPANY$Purchase Line].[Quantity Received] --- Inkoop order lijn, aantal ontvangen
,[COMPANY$Purch_ Rcpt_ Line].Quantity AS [COMPANY$Purch_ Rcpt_ Line Quantity] --- inkoop ontvangst lijn, aantal
,[COMPANY$Purch_ Inv_ Line].[Document No_] AS [COMPANY$Purch_ Inv_ Line Document No_] --- inkoop factuur lijn, documentnr
,[COMPANY$Purch_ Inv_ Line].Amount --- inkoop factuur lijn, bedrag
,[COMPANY$Purch_ Inv_ Header].[Posting Date] --- inkoop factuur header, datum
,[COMPANY$Purch_ Inv_ Header].[Vendor Invoice No_] --- inkoop factuur header, leveranciers nummer
,[COMPANY$Vendor].Name --- Leveranciers, naam leverancier
FROM
[COMPANY$Purchase Line] --- inkoop order lijn
LEFT OUTER JOIN [COMPANY$Purch_ Rcpt_ Line] --- left join met inkoop ontvangst lijn
--- documentnr van inkoop order lijn moet matchen met het order nr van de inkoop ontvangst lijn AND lijnnr van inkoop order lijn moet matchen met inkoop ontvangst lijn order lijn nr
ON [COMPANY$Purchase Line].[Document No_] = [COMPANY$Purch_ Rcpt_ Line].[Order No_] AND [COMPANY$Purchase Line].[Line No_] = [COMPANY$Purch_ Rcpt_ Line].[Order Line No_]
LEFT OUTER JOIN [COMPANY$Purch_ Inv_ Line] --- left join met inkoop factuur lijn
--- documentnr van inkoop ontvangst lijn matchen met inkoop factuur lijn nr AND lijn nr van inkoop ontvangst lijn matchen met inkoop ontvangst lijn nr
ON [COMPANY$Purch_ Rcpt_ Line].[Document No_] = [COMPANY$Purch_ Inv_ Line].[Receipt No_] AND [COMPANY$Purch_ Rcpt_ Line].[Line No_] = [COMPANY$Purch_ Inv_ Line].[Receipt Line No_]
LEFT OUTER JOIN [COMPANY$Purch_ Inv_ Header] --- left join met inkoop factuur header
--- document nr van inkoop factuur lijn matchen met nr van inkoop factuur header
ON [COMPANY$Purch_ Inv_ Line].[Document No_] = [COMPANY$Purch_ Inv_ Header].No_
INNER JOIN [COMPANY$Vendor] --- inner join met alle leveranciers
--- inkoop order lijn leveranciers nr matchen met leveranciers nr
ON [COMPANY$Purchase Line].[Buy-from Vendor No_] = [COMPANY$Vendor].No_
WHERE
[COMPANY$Purchase Line].[Document No_] LIKE N'BB%'
AND [COMPANY$Purchase Line].[Job No_] LIKE @JobNo_
above is the query now without the job ledger entry table. But on the job ledger entry table there is the one record that should also be included in my query.
Now i started my new query:
select *
from [COMPANY$Job Ledger Entry Cmfrt]
inner join [COMPANY$Purch_ Inv_ Line]
on [COMPANY$Job Ledger Entry Cmfrt].[Document No_] = [COMPANY$Purch_ Inv_ Line].[Document No_]
where [COMPANY$Job Ledger Entry Cmfrt].[Job No_] = 'P0002333' and [COMPANY$Job Ledger Entry Cmfrt].[Document No_] = 'IFG1903-00273'
I started my query to do thing the other way around. First get job ledger entry , then join it with purch inv line then purch inv header , purch rcpt line and at last the purchase line table.
But like you can see the other way around isn't working either. What is normal because on the 2 tables i match on things dat excist in every record. So there is no unique link between them
