Return data when the OR operator conditions are true in sql server

Question

I have the following query. Currently it checks on two conditions:

if one condition is true it will return the results for the first statement (t1.ticket=t2.ticket and ( t1.type=t2.type)
if this result is false then it will return results for the next condition ( t1.code=t2.code).

It does this is because sometimes this condition columns (t1.ticket=t2.ticket and ( t1.type=t2.type)) are equal to null and some times this condition colums ( t1.code=t2.code) are null thats why it switches between both.

But now what i noticed is that sometimes both the conditions return true and because of the OR statement its ignoring one of the conditions. How do i return results for both of those conditions if they both there conditions are met? If its not met then they must return the one condition that matches.

    select t1.name 
           ,t1.ID
           ,t1.type
           ,t2.TicketID
           ,t2.Account
           ,t1.code
    from table 1 t1 
    inner join table 2 t2 
            on (t1.ticketID=t2.ticketID and t1.type=t2.type) 
            or ( t1.code=t2.code)
    left join table 3 t3 
            on t2.Res=t3.res
    left join table 4 t4 
            on t3.IdDetail=t4.idDetail

Unclear what you are asking. Please include sample data for your question. Also, the query you did include would not even run on SQL Server. — Tim Biegeleisen
I read the question several times but it's still not clear what you're actually asking. Could you please provide sample data and desired output to clearify? — Robert Kock
Btw, it's best to add the simplified sample data & expected result as text. Makes it easier for the one who wants to spend their free time on it. (typing the mock data yourself takes more effort) — LukStorms

Zaynul Abadin Tuhin Zaynul Abadin Tuhin · Accepted Answer · 2018-10-11T07:44:31

you can try by using left join

    select t1.name 
               ,t1.ID
               ,COALESCE(t2.Ticket,t22.Ticket) as Ticket
               ,COALESCE(t2.Account,t22.Account) as Account
               ,COALESCE(t2.code,t22.code) as code
               ,t22.type
               ,t3.res
        ,t1.type
        from table1 t1 left join table2 t2 on (t1.ticket=t2.ticket and t1.code=t2.code) 
    left join t22 on ( t1.type=t22.type)
    left join table3 t3 on t2.Res=t3.res
left join table4 t4 on t3.IdDetail=t4.idDetail

If you want both result of table2 then no need use COALESCE function

Return data when the OR operator conditions are true in sql server

2 Answers