CODEWITHSUNDEEP
mysqlsql
neo
neo

Reputation: 457

Table1 Left Join Table2 Minus (join Data)

TABLE 1) m_conservationsetting

FacilityId   Unit   CategoryId
    1          1        1
    1          1        2
    1          1        3
    1          2        1
    1          2        2
    2          1        1
    2          2        1

Unique Key(FacilityId Unit CategoryId)

TABLE 2) l_maintelog

FacilityId   Unit  CategoryId  Status
    1          1        1         0
    1          1        2         1
    1          1        3         0
    1          2        1         0
    2          1        1         0
    2          2        1         0

Result :

FacilityId   Unit   CategoryId
    1          2        2

Table1 need to be left Joined with Table2 and it should ommit the join results and show only table1 data as results. Table1 LeftJoin Table2 - (join Data) for the below query. The condition for getting result is to check the status=0 for the record in Table2 

SELECT cs.FacilityId,Cs.Unit,cs.CategoryId 
FROM m_conservationsetting cs 
LEFT JOIN l_maintelog ml 
ON cs.FacilityId=ml.FacilityId and cs.Unit=ml.Unit
WHERE ml.Status=0 
GROUP BY cs.CategoryId

Upvotes: 2

Views: 812

Answers (2)

Mihir Shah
Mihir Shah

Reputation: 988

Only left join is enough to get the result.

Set Nocount On;

Declare @table1 Table
(
     FacilityId         Int
    ,Unit               Int
    ,CategoryId         Int
)

Declare @table2 Table
(
     FacilityId         Int
    ,Unit               Int
    ,CategoryId         Int
    ,[Status]           Bit
)

Insert Into @table1(FacilityId,Unit,CategoryId) Values
 (1,1,1)
,(1,1,2)
,(1,1,3)
,(1,2,1)
,(1,2,2)
,(2,1,1)
,(2,2,1)

Insert Into @table2(FacilityId,Unit,CategoryId,[Status]) Values
 (1,1,1,0)
,(1,1,2,1)
,(1,1,3,0)
,(1,2,1,0)
,(2,1,1,0)
,(2,2,1,0)

Select  t1.*
From    @table1 As t1
        Left Join @table2 As t2 On t1.FacilityId = t2.FacilityId
            And t1.Unit = t2.Unit
            And t1.CategoryId = t2.CategoryId
Where   t2.FacilityId Is Null

Output:-

enter image description here

Upvotes: 2

Mobasher Fasihy
Mobasher Fasihy

Reputation: 1061

If you only want to take those records that are not in result of left join so do like this:

SELECT t.* FROM m_conservationsetting AS t
WHERE NOT EXISTS (
    SELECT cs.FacilityId,Cs.Unit,cs.CategoryId 
    FROM m_conservationsetting AS cs 
    LEFT JOIN l_maintelog ml on
       (cs.FacilityId=ml.FacilityId and cs.Unit=ml.Unit)
    WHERE ml.Status=0 
    group by cs.CategoryId
)

Upvotes: 2

Related Questions