Self join table where results list the same column twice with different results

Question

edit:

I have a db table with two columns: id and dep, that lists all department names.

is it possible to run a query (either self join or subquery) that returns the dep column twice (dep1 and dep2).

dep1 would like all departments.

dep2 would list only departments that contain an &

I hoped this would be possible but have yet to find/test a query that works.

Am I out to lunch on this?

original post:

My need is to create a query to return the same column twice--the first column showing all results and the second column showing only names with special characters (& and ').

I have two queries, below, that I would like to turn into a self join.

SELECT dep
FROM `test_deps`
where dep like "%&%" or dep like "%'%"

Returns:

dep (col name)
CAPS - Dean's Office
Parking & Transportation


SELECT dep
FROM `test_deps`
where dep not like "%&%" or dep not like "%'%"

Returns:

dep (col name)
Admissons

I a query that returns:

dep1                            dep2
Admissions
CAPS - Dean's Office            CAPS - Dean's Office
Parking & Transportation        Parking & Transportation

I tried this query that obviously does not work.

SELECT h1.dep as dep1, h2.dep as dep2
FROM test_deps h1
INNER JOIN test_deps h2
ON h1.id=h2.id
WHERE h1.dep like "%&%"
or h1.dep like "%'%"
or h2.dep not like "%&%"
AND h2.dep not like "%'%" 

Maybe I need to turn h2.dep as dep in the select into a query.

Answer 1

Use conditional expressions.

SELECT id, 
       CASE WHEN dep like "%&%" or dep like "%'%"
            THEN dep 
            END dep1,
       CASE WHEN dep not like "%&%" or dep not like "%'%"
            THEN dep 
            END dep2
FROM `test_deps`
-- HAVING dep1 IS NOT NULL AND dep2 IS NOT NULL
;

If you do not need in the rows which does not match both conditions (for example, dep = NULL) then uncomment HAVING clause.

If you need in empty string instead of NULLs in the output then add ELSE '' to both CASEs (and edit HAVING conditions accordingly).