Dynamic/Conditional SQL Join?

sql sql-server join

1,452

Solution 1

SELECT [dbo].tableB.theColumnINeed
FROM   [dbo].tableA 
LEFT OUTER JOIN [dbo].tableB
ON [dbo].tableA.myColumn = 
   CASE
    WHEN [dbo].tableA.myDateColumn <= '1/1/2009' THEN FormatColumnOneWay([dbo].tableB.myColumn)
    ELSE FormatColumnAnotherWay([dbo].tableB.myColumn)
   END

Solution 2

Rather than having a CASE statement in the JOIN, which will prevent the query using indexes, you could consider using a UNION

SELECT [dbo].tableB.theColumnINeed 
FROM   [dbo].tableA 
    LEFT OUTER JOIN [dbo].tableB 
         ON [dbo].tableA.myDateColumn > '1/1/2009'
        AND [dbo].tableA.myColumn = FormatColumnOneWay([dbo].tableB.myColumn)
UNION ALL
SELECT [dbo].tableB.theColumnINeed 
FROM   [dbo].tableA 
    LEFT OUTER JOIN [dbo].tableB 
         ON [dbo].tableA.myDateColumn <= '1/1/2009'
        AND [dbo].tableA.myColumn = FormatColumnAnotherWay([dbo].tableB.myColumn)

but if the FormatColumnOneWay / FormatColumnAnotherWay are functions, or field expressions, that is probably going to exclude use of inxdexes on [myColumn], although any index on myDateColumn should still be used

However, it might help to understand what the FormatColumnOneWay / FormatColumnAnotherWay logic is, as knowning that may enable a better optimisation

Couple of things to note:

UNION ALL will not remove any duplicates (unlike UNION). Because the two sub-queries are mutually exclusive this is OK and saves the SORT step which UNION would make to enable it to remove duplicates.

You should not use '1/1/2009' style for string-dates, you should use 'yyyymmdd' style without and slashes or hyphens (you can also use CONVERT with an parameter to explicitly indicate that the string is in d/m/y or m/d/y style

1,452

Author by

RohitMallampati

Updated on June 04, 2022

Comments

RohitMallampati almost 2 years
I have a dictionary which has values as:
```
m = {1: 2, 7: 3, 2: 1, 4: 4, 5: 3, 6: 9}
```
The required output should be cyclic values like 1:2 -> 2:1 = cycle, which both are present in dictionary. 4:4 is also a cycle.

My output should be
```
[(1, 2), [4]]
```
CODE
```
m = {1: 2, 7: 3, 2: 1, 4: 4, 5: 3, 6: 9}
k = list(m.items())
print(k)

p = [t[::-1] for t in k]
print(p)

my_list = [(a, b) for (a, b) in p for (c, d) in k  if ((a ==c) and (b == d))]

for i in k:
     if i in my_list:
             print("cycles : ", i)
```
OUTPUT:

The output I am getting is
```
cycles : (1, 2)
cycles : (2, 1)
cycles : (4, 4)
```
Can someone help me out with this?
- Mad Physicist almost 5 years
  
  Why is (2,1) a tuple but [4] a list?
- Mad Physicist almost 5 years
  
  Also, can the dictionary contain None values?
- Mark almost 5 years
  
  Can the cycles be longer than two elements?
- Mad Physicist almost 5 years
  
  @MarkMeyer. I've added that to my answer
Mad Physicist almost 5 years

Removing while iterating will give unpredictable results
Mad Physicist almost 5 years

Or rather predictable but not intended
manik venkat almost 5 years

yeah now i edited the solution. which replaces instead of removing.