Relational Division Sample Query


  

CREATE TABLE tbl_Division(
   DivisionID   INT  NOT NULL PRIMARY KEY 
  ,DivisionName VARCHAR(100) NOT NULL
);
INSERT INTO tbl_Division(DivisionID,DivisionName) 
VALUES (1,'Division A'),  (2,'Division B'), (3,'Division C');


CREATE TABLE tbl_Region(
   RegionID INT  NOT NULL PRIMARY KEY 
  ,RegionName VARCHAR(100) NOT NULL
);
INSERT INTO tbl_Region(RegionID,RegionName) 
VALUES (1,'North America'),(2,'South America'),(3,'Europe');


CREATE TABLE tbl_DivisionRegion(
   DivisionRegionID INT  NOT NULL PRIMARY KEY 
  ,DivisionID       INT  NOT NULL
  ,RegionID         INT  NOT NULL
);

ALTER TABLE tbl_DivisionRegion
ADD FOREIGN KEY (DivisionID) REFERENCES tbl_Division(DivisionID);
ALTER TABLE tbl_DivisionRegion
ADD FOREIGN KEY (RegionID) REFERENCES tbl_Region(RegionID);


INSERT INTO tbl_DivisionRegion(DivisionRegionID,DivisionID,RegionID) 
VALUES (1,1,1),(2,1,2),(3,2,3), (4,3,1),(5,3,3);


--Option 1 to use EXISTS

--Question 1

SELECT d.DivisionName, r.RegionName
FROM tbl_Division d
LEFT JOIN tbl_DivisionRegion dr ON d.DivisionID = dr.DivisionID
LEFT JOIN tbl_Region r ON dr.RegionID = r.RegionID 
WHERE 
Exists (Select 1 from tbl_DivisionRegion dr2 JOIN tbl_Region r ON dr2.RegionID = r.RegionID where d.DivisionID=dr2.DivisionID and r.RegionName ='North America' )
AND
Exists (Select 1 from tbl_DivisionRegion dr2 JOIN tbl_Region r ON dr2.RegionID = r.RegionID where d.DivisionID=dr2.DivisionID and r.RegionName ='South America' )

ORDER BY dr.DivisionID, dr.RegionID
/*
DivisionName	RegionName
Division A	North America
Division A	South America
*/

--Question 2
SELECT d.DivisionName, r.RegionName
FROM tbl_Division d
LEFT JOIN tbl_DivisionRegion dr ON d.DivisionID = dr.DivisionID
LEFT JOIN tbl_Region r ON dr.RegionID = r.RegionID 
WHERE 
Exists (Select 1 from tbl_DivisionRegion dr2 JOIN tbl_Region r ON dr2.RegionID = r.RegionID where d.DivisionID=dr2.DivisionID and r.RegionName ='North America' )
AND
Exists (Select 1 from tbl_DivisionRegion dr2 JOIN tbl_Region r ON dr2.RegionID = r.RegionID where d.DivisionID=dr2.DivisionID and r.RegionName ='Europe' )

ORDER BY dr.DivisionID, dr.RegionID
/*

DivisionName	RegionName
Division C	North America
Division C	Europe


*/

--Question 3
SELECT d.DivisionName, r.RegionName
FROM tbl_Division d
LEFT JOIN tbl_DivisionRegion dr ON d.DivisionID = dr.DivisionID
LEFT JOIN tbl_Region r ON dr.RegionID = r.RegionID 
WHERE 
Exists (Select 1 from tbl_DivisionRegion dr2 JOIN tbl_Region r ON dr2.RegionID = r.RegionID where d.DivisionID=dr2.DivisionID and r.RegionName ='North America' )
AND
Exists (Select 1 from tbl_DivisionRegion dr2 JOIN tbl_Region r ON dr2.RegionID = r.RegionID where d.DivisionID=dr2.DivisionID and r.RegionName ='South America' )
AND
Exists (Select 1 from tbl_DivisionRegion dr2 JOIN tbl_Region r ON dr2.RegionID = r.RegionID where d.DivisionID=dr2.DivisionID and r.RegionName ='Europe' )

ORDER BY dr.DivisionID, dr.RegionID
/*

DivisionName	RegionName

*/

 
---Option 2
 --Pass your region checklist
 declare @RegionName1 varchar(50)='North America'  
 declare @RegionName2 varchar(50)= 'South America' -- NULL
 declare @RegionName3 varchar(50)=   NULL   --'Europe'  


 ;with checklist as (
 Select RegionName from (values (@RegionName1),(@RegionName2) ,(@RegionName3)) d(RegionName )
 )

 ,mycteSrc as (
 SELECT d.DivisionName, d.divisionID, r.RegionID,r.RegionName
FROM tbl_Division d
LEFT JOIN tbl_DivisionRegion dr ON d.DivisionID = dr.DivisionID
LEFT JOIN tbl_Region r ON dr.RegionID = r.RegionID
 )
 
 ,mycteProcessing as (
 SELECT src.DivisionName, CASE WHEN COUNT(src.RegionName) > (SELECT COUNT(RegionName) FROM checklist ) AND COUNT(ckl.RegionName) = (SELECT COUNT(RegionName)FROM checklist )
            THEN 'match with extra'
            WHEN COUNT(src.RegionName) = (SELECT COUNT(RegionName) FROM checklist) AND COUNT(ckl.RegionName) = (SELECT COUNT(RegionName) FROM checklist)
            THEN 'exactly match'
            WHEN MIN(ckl.RegionName) IS NULL
            THEN 'none'
            ELSE 'some' END AS location_status
  FROM   mycteSrc AS src LEFT JOIN  checklist AS ckl   ON src.RegionName = ckl.RegionName
  GROUP BY src.DivisionName )
 
  Select a.DivisionName,a.RegionName  
  from mycteSrc a join mycteProcessing m 
  on a.DivisionName =m.DivisionName 
   Where m.location_status='exactly match'

 



 -- option 3

;with checklist as (
 Select @RegionName1 as RegionName
 Union all
  Select @RegionName2 as RegionName
 Union all
  Select @RegionName3 as RegionName 
 )

 ,mycteSrc as (
 SELECT d.DivisionName, d.divisionID, r.RegionID,r.RegionName
FROM tbl_Division d
LEFT JOIN tbl_DivisionRegion dr ON d.DivisionID = dr.DivisionID
LEFT JOIN tbl_Region r ON dr.RegionID = r.RegionID
 )
 
 
 SELECT src.DivisionName           
  FROM   mycteSrc AS src LEFT JOIN  checklist AS checklist   
  ON src.RegionName = checklist.RegionName
   GROUP BY src.DivisionName     
  Having( COUNT(src.RegionName) = (SELECT COUNT(RegionName) FROM checklist) 
  AND COUNT(checklist.RegionName) = (SELECT COUNT(RegionName) FROM checklist) 
  )
 

 



drop table  tbl_DivisionRegion,tbl_Division, tbl_Region 

--You can search the term Relational division to learn more on this topic


 

https://forums.asp.net/t/2127017.aspx?MS+SQL+Query+Exclusive+Where

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s