MySQL Forums :: General :: I need to better understand how to use self joins

Consider the following table:

CREATE TABLE HOSTS(
hostname VARCHAR(100)
);

INSERT INTO HOSTS
VALUES
('SERVER-100'),
('SERVER-200'),
('TERMINAL1-100'),
('TERMINAL1-200'),
('TERMINAL2-200'),
('TERMINAL1-300'),
('TERMINAL2-300');

I would like a query that will identify the '300' component of the hostname field where there is no server for that site ID.

The query I have is:

SELECT DISTINCT substring_index(hostname,'-',-1) AS site
FROM `hosts` 
WHERE 
substring_index(hostname,'-',-1) IN 
(
	SELECT DISTINCT substring_index(hostname,'-',-1) FROM `hosts`  WHERE hostname LIKE 'TERM%'
)
AND 
substring_index(hostname,'-',-1)  NOT IN 
(
	SELECT DISTINCT substring_index(hostname,'-',-1) FROM `hosts`  WHERE hostname LIKE 'SERV%'
)

This works but is painfully slow in my production DB which has ~10000 rows.

Is there a more scalable approach to this problem?

Thanks,

Shawn