i have a table in that table 4 column Id , FirmId, OfficeId, PlanID
Data Like Below
Id FirmId OfficeId PlanID
------------------------------
1 54 0 4
2 54 3 9
3 54 5 8
4 54 6 4
5 54 7 9
6 54 8 8
FirmID 54 has 10 Office
if office does not conains plan then it will refer a firmid and officeid Null planid
like Office 1,2,9,10 does not contains plan so it will refer a FirmId = 54 And Office id = 0 Plan
Now i want result Like Below
Id FirmId OfficeId PlanID
-----------------------------------------
1 54 1 4
1 54 2 4
2 54 3 9
3 54 5 8
4 54 6 4
5 54 7 9
6 54 8 8
1 54 9 4
1 54 10 4
what have you tried? Share your query, even if it doesn't work
i have 4 Table Firm, Office, Plan, FirmOfficePlan in FirmOfficePlan Contains Firm plan and Office Plan like firm 54 has a 10 office Firm 54 has a plan id = 4 and Office id = NULL
but in Some of Office of 54 has a different plan like Office 3 has a Plan id = 9 ,Office 5 has a plan id = 8, Office 6 has a planid = 6
so rest of office has a refer a plan = 4 (Which is configure for Firm)
Below query i had try but i am not satisfy
SELECT A.Firm_Id,
A.Office_id,
PricingPlanId = COALESCE(B.PlanId,A.PlanId)
FROM (SELECT A.Firm_Id,O.Office_id,A.PlanId
FROM Firm F
Inner Join Office O ON O.Firm_Id = F.Firm_Id
Left Join FirmOfficePlan A ON A.Firm_Id = F.Firm_ID
AND A.Office_Id IS Null
WHERE F.Firm_ID = 54)A
LEFT JOIN FirmOfficePlan B ON A.Firm_ID = B.Firm_ID
AND A.Office_id = B.Office_Id
AND B.Office_Id IS NOT NULL