I have two tables and i joined them using inner join two get the required result. When i execute the tables separately then i am getting the result.
SELECT
LINE_NUMBER
,ITEM_DESCRIPTION
,QUANTITY
FROM [ELIT].[ATS_SRC_DOCUMENT_LINES_V]
WHERE LANGUAGE = 'EN'
AND DOCUMENT_ID = 2087
SELECT
SUPPLIER_NAME
,CONCAT_WS(' ', CAST([QUOTE_TOTAL] AS NUMERIC(18,2)),[QUOTE_CURRENCY_CODE]) AS QUOTE_VALUE
FROM [ELIT].[ATS_SRC_SUPPLIER_HEADERS_V]
WHERE LANGUAGE = 'EN'
AND DOCUMENT_ID = 2087
If i join both the tables and execute i am getting duplicate result for each data
SELECT
ASSH.SUPPLIER_NAME
,CONCAT_WS(' ', CAST([QUOTE_TOTAL] AS NUMERIC(18,2)),[QUOTE_CURRENCY_CODE]) AS QUOTE_VALUE
,ASDL.LINE_NUMBER
,ASDL.QUANTITY
,ASDL.ITEM_DESCRIPTION
from [ELIT].[ATS_SRC_SUPPLIER_HEADERS_V] ASSH
INNER JOIN [ELIT].[ATS_SRC_DOCUMENT_LINES_V] ASDL
ON ASDL.DOCUMENT_ID = ASSH.DOCUMENT_ID
AND ASDL.LANGUAGE = ASSH.LANGUAGE
AND ASSH.LANGUAGE = 'EN'
AND ASSH.DOCUMENT_ID = 2087
What is the proper way to get the required result? thanks in advance...
What I have tried:
I tried using DISTINCT and GROUP BY but the result is the same.
SELECT DISTINCT
ASSH.SUPPLIER_NAME
,CONCAT_WS(' ', CAST([QUOTE_TOTAL] AS NUMERIC(18,2)),[QUOTE_CURRENCY_CODE]) AS QUOTE_VALUE
,ASDL.LINE_NUMBER
,ASDL.QUANTITY
,ASDL.ITEM_DESCRIPTION
FROM [ELIT].[ATS_SRC_SUPPLIER_HEADERS_V] ASSH
INNER JOIN [ELIT].[ATS_SRC_DOCUMENT_LINE_DETAILS] ASDL
ON ASDL.DOCUMENT_ID = ASSH.DOCUMENT_ID
--AND ASDL.LANGUAGE = ASSH.LANGUAGE
AND ASSH.LANGUAGE = 'en'
AND ASSH.DOCUMENT_ID = 2087
GROUP BY LINE_NUMBER, QUANTITY, ITEM_DESCRIPTION, SUPPLIER_NAME, QUOTE_TOTAL, QUOTE_CURRENCY_CODE