Click here to Skip to main content
15,868,141 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
SQL



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
SQL



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
SQL

Posted
Updated 3-Jan-23 19:55pm
Comments
Herman<T>.Instance 5-Jan-23 3:05am    
Did you try UNION ALL between both queries?

1 solution

Without your DB, we can't tell - we have no way to run your queries and no idea what result you expect.

So it's going to be up to you. Start by removing the "extras" from your JOIN query so the JOIN conditions are the same as the SELECT conditions in the separate queries, and add the ASDL.DOCUMENT_ID, ASSH.DOCUMENT_ID, ASDL.LANGUAGE, and ASSH.LANGUAGE fields to the SELECT list.
Then compare values - almost certainly there are no records where the ID and Language fields actually match which means no rows would be returned.
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900