I am new to SQL queries ,could any one help me on this query, I have two tables below , need to join these tables location such that suppose I have both Etype on a same date the preference must be for Etype B over A, if there is not B then only fetch A

Table1

Table2

Result is expected like:

CodePudding user response：

In case you need to eliminate ties, or there are more than two Etypes, or they are not actually alphabetical in real life, or you have other columns you haven't mentioned that you can't or don't want to aggregate:

;WITH loc AS 
(
  SELECT location, date, Etype,
    rn = ROW_NUMBER() OVER (PARTITION BY location, date
         ORDER BY CASE Etype WHEN 'B' THEN 1
           WHEN 'A' THEN 2
           -- other conditions
           END)
  FROM dbo.table2
)
SELECT t1.Id, t1.location, loc.date, loc.Etype
FROM loc
INNER JOIN dbo.table1 AS t1
ON loc.location = t1.location
  AND loc.rn = 1;

• Example db<>fiddle

CodePudding user response：

You can use a simple aggregation presuming you only two types A and B for Etype

SELECT t1.id, t2.location, t2.date, MAX(t2.Etype)
  FROM [table1] AS t1
  JOIN [table2] AS t2
    ON t2.location = t1.location
 GROUP BY t2.id, t2.location, t2.date

CodePudding user response：

SELECT T.ID,T.LOCATION,X.DATE,MAX(ETYPE)MAX_ETYPE
FROM TABLE_1 AS T
JOIN TABLE_2 AS X ON T.LOCATION=X.LOCATION
GROUP BY T.ID,T.LOCATION,X.DATE

Could you please try the above ?