The following image is a part of Microsoft SQL Server 2008 R2 System Views. From the image we can see that the relationship between sys.partitions
and sys.allocation_units
depends on the value of sys.allocation_units.type
. So to join them together I would write something similar to this:
SELECT *
FROM sys.indexes i
JOIN sys.partitions p
ON i.index_id = p.index_id
JOIN sys.allocation_units a
ON CASE
WHEN a.type IN (1, 3)
THEN a.container_id = p.hobt_id
WHEN a.type IN (2)
THEN a.container_id = p.partition_id
END
But the upper code gives a syntax error. I guess that's because of the CASE
statement. Can anyone help to explain a little?
Add error message:
Msg 102, Level 15, State 1, Line 6 Incorrect syntax near '='.
https://i.stack.imgur.com/HYmiS.jpg
A CASE
expression returns a value from the THEN
portion of the clause. You could use it thusly:
SELECT *
FROM sys.indexes i
JOIN sys.partitions p
ON i.index_id = p.index_id
JOIN sys.allocation_units a
ON CASE
WHEN a.type IN (1, 3) AND a.container_id = p.hobt_id THEN 1
WHEN a.type IN (2) AND a.container_id = p.partition_id THEN 1
ELSE 0
END = 1
Note that you need to do something with the returned value, e.g. compare it to 1. Your statement attempted to return the value of an assignment or test for equality, neither of which make sense in the context of a CASE
/THEN
clause. (If BOOLEAN
was a datatype then the test for equality would make sense.)
Instead, you simply JOIN to both tables, and in your SELECT clause, return data from the one that matches:
I suggest you to go through this link Conditional Joins in SQL Server and T-SQL Case Statement in a JOIN ON Clause
e.g.
SELECT *
FROM sys.indexes i
JOIN sys.partitions p
ON i.index_id = p.index_id
JOIN sys.allocation_units a
ON a.container_id =
CASE
WHEN a.type IN (1, 3)
THEN p.hobt_id
WHEN a.type IN (2)
THEN p.partition_id
END
Edit: As per comments.
You can not specify the join condition as you are doing.. Check the query above that have no error. I have take out the common column up and the right column value will be evaluated on condition.
conditional join
means? Each join (excluding cross) is a conditional. How this case is different from any other? Your sample has inner join with condition, as well as OPs query has join with condition.
OR
?
OR
". Using OR
is merely one way of implementing it.
OR
and 2 ANDs
or with 1 CASE
. It is a routine query that has nothing that differs it from any other.
Try this:
...JOIN sys.allocation_units a ON
(a.type=2 AND a.container_id = p.partition_id)
OR (a.type IN (1, 3) AND a.container_id = p.hobt_id)
I think you need two case statements:
SELECT *
FROM sys.indexes i
JOIN sys.partitions p
ON i.index_id = p.index_id
JOIN sys.allocation_units a
ON
-- left side of join on statement
CASE
WHEN a.type IN (1, 3)
THEN a.container_id
WHEN a.type IN (2)
THEN a.container_id
END
=
-- right side of join on statement
CASE
WHEN a.type IN (1, 3)
THEN p.hobt_id
WHEN a.type IN (2)
THEN p.partition_id
END
This is because:
the CASE statement returns a single value at the END
the ON statement compares two values
your CASE statement was doing the comparison inside of the CASE statement. I would guess that if you put your CASE statement in your SELECT you would get a boolean '1' or '0' indicating whether the CASE statement evaluated to True or False
Yes, you can. Here is an example.
SELECT a.*
FROM TableA a
LEFT OUTER JOIN TableB j1 ON (CASE WHEN LEN(COALESCE(a.NoBatiment, '')) = 3
THEN RTRIM(a.NoBatiment) + '0'
ELSE a.NoBatiment END ) = j1.ColumnName
I took your example and edited it:
SELECT *
FROM sys.indexes i
JOIN sys.partitions p
ON i.index_id = p.index_id
JOIN sys.allocation_units a
ON a.container_id = (CASE
WHEN a.type IN (1, 3)
THEN p.hobt_id
WHEN a.type IN (2)
THEN p.partition_id
ELSE NULL
END)
This seems nice
https://bytes.com/topic/sql-server/answers/881862-joining-different-tables-based-condition
FROM YourMainTable
LEFT JOIN AirportCity DepCity ON @TravelType = 'A' and DepFrom = DepCity.Code
LEFT JOIN AirportCity DepCity ON @TravelType = 'B' and SomeOtherColumn = SomeOtherColumnFromSomeOtherTable
Here I have compared the difference in two different result sets:
SELECT main.ColumnName, compare.Value PreviousValue, main.Value CurrentValue
FROM
(
SELECT 'Name' AS ColumnName, 'John' as Value UNION ALL
SELECT 'UserName' AS ColumnName, 'jh001' as Value UNION ALL
SELECT 'Department' AS ColumnName, 'HR' as Value UNION ALL
SELECT 'Phone' AS ColumnName, NULL as Value UNION ALL
SELECT 'DOB' AS ColumnName, '1993-01-01' as Value UNION ALL
SELECT 'CreateDate' AS ColumnName, '2017-01-01' as Value UNION ALL
SELECT 'IsActive' AS ColumnName, '1' as Value
) main
INNER JOIN
(
SELECT 'Name' AS ColumnName, 'Rahul' as Value UNION ALL
SELECT 'UserName' AS ColumnName, 'rh001' as Value UNION ALL
SELECT 'Department' AS ColumnName, 'HR' as Value UNION ALL
SELECT 'Phone' AS ColumnName, '01722112233' as Value UNION ALL
SELECT 'DOB' AS ColumnName, '1993-01-01' as Value UNION ALL
SELECT 'CreateDate' AS ColumnName, '2017-01-01' as Value UNION ALL
SELECT 'IsActive' AS ColumnName, '1' as Value
) compare
ON main.ColumnName = compare.ColumnName AND
CASE
WHEN main.Value IS NULL AND compare.Value IS NULL THEN 0
WHEN main.Value IS NULL AND compare.Value IS NOT NULL THEN 1
WHEN main.Value IS NOT NULL AND compare.Value IS NULL THEN 1
WHEN main.Value <> compare.Value THEN 1
END = 1
There are at least 2 ways to join based on condition. One is faster than the other:
declare @loopZaKosovnice int = 1
select *
from tHE_MoveItem mi
left join tHE_SetProdSt st on st.acIdent = mi.acIdent
-- slow
--join the_setitem si on si.acident = case when @loopZaKosovnice = 0 then mi.acident else st.acIdentChild end
-- two times as fast
left join the_setitem si1 on @loopZaKosovnice = 0 and si1.acident = mi.acident
left join the_setitem si2 on @loopZaKosovnice = 1 and si2.acident = st.acIdentChild
join the_setitem si on si.acident = isnull (si1.acident, si2.acIdent)
Took DonkeyKong's example.
The issue is I needed to use a declared variable. This allows for stating your left and right-hand side of what you need to compare. This is for supporting an SSRS report where different fields must be linked based on the selection by the user.
The initial case sets the field choice based on the selection and then I can set the field I need to match on for the join.
A second case statement could be added for the right-hand side if the variable is needed to choose from different fields
LEFT OUTER JOIN Dashboard_Group_Level_Matching ON
case
when @Level = 'lvl1' then cw.Lvl1
when @Level = 'lvl2' then cw.Lvl2
when @Level = 'lvl3' then cw.Lvl3
end
= Dashboard_Group_Level_Matching.Dashboard_Level_Name
Success story sharing
= 1
to= 0
, but I don't think you'll like the result.