SQL Joining another table with condition

Question

I have two tables given below

service table

serviceid      serviceName     title   isrestricted
---           --------------  ------  ----------
1                  abc          t1     0
2                  asd          t2     1
3                  def          t3     0


serviceRestricted table

sampletype   serviceid
--------     ---------
a            2


output

 serviceid      serviceName     title   isrestricted
    ---           --------------  ------  ----------
    1                  abc          t1     0   
    3                  def          t3     0

See the output .For Service id 2 isrestricted=1 and there is entry in the servicerestricted table. So serviceid 2 is not visible in the output

My requirement is that if it isrestricted=1 then it should join with servicerestricted table else donot join with the servicerestricted table — sandeep.mishra, Oct 02 '19 at 06:35
If you don't understand your own question such that you cannot show us the expected output, then you are not ready to ask here. — Tim Biegeleisen, Oct 02 '19 at 06:38
Sorry if you cant understood me. I have given the output .Please check my edited question — sandeep.mishra, Oct 02 '19 at 06:43

score 0 · Answer 1 · answered Oct 02 '19 at 07:12

0

Left Join should solve your problem as:

select S.serviceid,S.serviceName,S.title,S.isrestricted
from @service S
Left Join @serviceRestricted SR on SR.serviceid = S.serviceid
where S.isrestricted = 1

Sample code here..

answered Oct 02 '19 at 07:12

Deepshikha

9,896
2
21
21

The other services with id 1 and 3 is not coming in the result. So left join will not work here – sandeep.mishra Oct 02 '19 at 07:23
if you want to include them then what's the point of adding condition with `isrestricted = 1`. Please post exactly the result you want .. – Deepshikha Oct 02 '19 at 07:26
if it is restricted then only it should look at the servicerestricted table with the id . Hope it is clear to you – sandeep.mishra Oct 02 '19 at 07:29

score 0 · Answer 2 · answered Oct 03 '19 at 09:05

I am able to achive it by below query

select S.serviceid,S.serviceName,S.title,S.isrestricted
from service S
WHERE
((S.IsRestricted =1 and S.serviceid NOT IN (SELECT SERVICEID FROM servicerestricted WHERE SampleType=@SampleType) ) 
OR (S.IsRestricted is null OR S.isrestricted =0))

SQL Joining another table with condition

2 Answers2