MySQL Forums
Forum List  »  General

I need to better understand how to use self joins
Posted by: Shawn Taylor
Date: April 16, 2012 08:44AM

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

Options: ReplyQuote


Subject
Written By
Posted
I need to better understand how to use self joins
April 16, 2012 08:44AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.