CODEWITHSUNDEEP
sqlms-accessleft-joinmultiple-conditions
sergiol
sergiol

Reputation: 4335

Left join with two exclusive conditions

I have two tables V and E and I need to do a strange LEFT JOIN.

In the E table there are the columns:

In the V table there is a column:

I tried the following query:

SELECT DSC,
COUNT(IIF([Cost] IS NOT NULL, 1, NULL)) AS [N Cost]
, INT(SUM(IIF([Cost] IS NOT NULL, [Cost], 0))) AS [Total Cost]
, INT(SUM(IIF([Fees] IS NOT NULL, Fees, 0))) AS [Total Fees]
 LEFT JOIN E ON LEFT(V.P_OR_O,6)=E.COD_Obj 
  OR RIGHT(V.P_OR_O,3)=E.COD_P
 GROUP BY DSC
ORDER BY DSC

It does the grouping correct, but each group returns TOO MUCH of N Cost, Total Cost, and Total Fees. I suspect there are things repeated on the result of this query.

And if I change the OR of the LEFT JOIN to be an AND, I will see only one line, corresponding to only one group.

I am dazzled and confused. Please help on how to do a match with two alternative criteria. And, before you ask, yes, the V.P_OR_O field can have empty/null values.

And no, I can't change the data model at this moment because, people depends on this database and I can not control the Server to redefine the database structure.

The tables are both in SQL server and I am using Microsoft Office's Access to query against them.

Upvotes: 1

Views: 251

Answers (1)

Ghost
Ghost

Reputation: 2226

You should be able to add a length into your join condition, something like this:

SELECT 
DSC
, COUNT(IIF([Cost] IS NOT NULL, 1, NULL)) AS [N Cost]
, INT(SUM(IIF([Cost] IS NOT NULL, [Cost], 0))) AS [Total Cost]
, INT(SUM(IIF([Fees] IS NOT NULL, Fees, 0))) AS [Total Fees]
FROM V
LEFT JOIN 
E 
ON   (LEFT(V.P_OR_O,6)=E.COD_Obj AND LEN(IIF(ISNULL(P_OR_O),'',P_OR_O)) = 9)
  OR (RIGHT(V.P_OR_O,3)=E.COD_P AND LEN(IIF(ISNULL(P_OR_O),'',P_OR_O)) = 12)
GROUP BY DSC
ORDER BY DSC

Upvotes: 0

Related Questions