Or not both sentences in select sql

Question

Find the names of all departments located in either BOSTON or DALLAS, not in both cities.

I have code like this

SELECT D.DNAME 
FROM DEPARTMENT D 
INNER JOIN DEPTLOC L ON L.DNAME = D.DNAME 
WHERE L.CITY='BOSTON' 
OR L.CITY='DALLAS' ;

But it will show the department located in BOSTON OR DALLAS. But I just want to either enter what I have to put in order to get the result.

Example: in my DEPTLOC table

     //DEPTLOC
     DNAME      CITY
     ----------------
     ACCOUNTING  BOSTON
     ACCOUNTING  DALLAS
     SALES       DALLAS
     TRANSPORT   BOSTON
     TRANSPORT   DALLAS

So, in my department I should get an output like

      DNAME
      ----------
      SALES

Answer 1

Group them, then calculate the total for each department, then filter out all departments that have only one location.

SELECT D.DNAME 
FROM DEPARTMENT D 
INNER JOIN DEPTLOC L ON L.DNAME = D.DNAME 
WHERE L.CITY='BOSTON' 
OR L.CITY='DALLAS'
GROUP BY
D.DNAME
HAVING COUNT(1) = 1

Answer 2

Try the following:

SELECT D.DNAME 
FROM DEPARTMENT D 
INNER JOIN DEPTLOC L ON L.DNAME = D.DNAME 
GROUP BY D.DNAME
HAVING 1 = SUM(CASE WHEN L.CITY IN ('BOSTON', 'DALLAS') THEN 1 ELSE 0 END);

Answer 3

:

SELECT department.dname
  FROM department
  JOIN deptloc
    ON department.dname = deptloc.dname
 WHERE deptloc.city IN ('BOSTON', 'DALLAS')
 GROUP
    BY department.dname
HAVING COUNT(DISTINCT deptloc.city) = 1
;

, deptloc.dname department.name, :

SELECT dname
  FROM deptloc
 WHERE city IN ('BOSTON', 'DALLAS')
 GROUP
    BY dname
HAVING COUNT(DISTINCT city) = 1
;

Answer 4

-

--take only those that ara in one city
SELECT DNAME_WITH_COUNT.DNAME FROM
        --count how many times it occurs
        (SELECT DNAME, COUNT(DNAME) CNT FROM
            --your select with both cities
            (SELECT D.DNAME 
            FROM DEPARTMENT D 
            INNER JOIN DEPTLOC L ON L.DNAME = D.DNAME 
            WHERE L.CITY='BOSTON' 
            OR L.CITY='DALLAS'
            ) 
        )DNAME_WITH_COUNT
    WHERE CNT>1;

Answer 5

You can join a table departmentwith an aggregate query that returns only one location for a department:

SELECT     d.dname
FROM       department d
INNER JOIN (SELECT   dname
            FROM     deptloc
            WHERE    city IN ('BOSTON', 'DALLAS')
            GROUP BY dname
            HAVING   COUNT(*) = 1) l ON l.dname = d.dname