Skip navigation links

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


Advanced Search

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 Shawn Taylor 04/16/2012 08:44AM
Re: I need to better understand how to use self joins laptop alias 04/16/2012 10:31AM
Re: I need to better understand how to use self joins Rick James 04/17/2012 09:31PM
Re: I need to better understand how to use self joins Shawn Taylor 04/24/2012 01:05PM
Re: I need to better understand how to use self joins Peter Brawley 04/24/2012 03:36PM


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.