MySQL Forums :: Newbie :: Query To Find the Number of Self-Joins?


Advanced Search

Query To Find the Number of Self-Joins?
Posted by: John Kim ()
Date: May 16, 2010 12:30AM

I need help with figuring out how many self-joins I need to do. Here's my problem:

I have a table containing unique image filenames:
Quote

CREATE TABLE IF NOT EXISTS `images` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `filename` varchar(64) NOT NULL,
  PRIMARY KEY (`id`)
);
INSERT INTO `images` (`id`, `filename`) VALUES
(1, 'face12417.jpg'),
(2, 'face15518.jpg'),
(3, 'face17123.jpg'),
...
(43229, 'face43824.jpg');
The images are of photos of people's faces. I have another table representing pairwise comparison of the photos, and those pairs that match (i.e., it's the same person appearing in both photos) are indicated with 'y', like this:
Quote

CREATE TABLE IF NOT EXISTS `pairs` (
  `id1` smallint(5) unsigned NOT NULL,
  `id2` smallint(5) unsigned NOT NULL,
  `match` char(1) NOT NULL DEFAULT '',
  PRIMARY KEY (`id1`,`id2`)
);

INSERT INTO `pairs` (`id1`, `id2`, `match`) VALUES
(1, 2, 'n'),
(1, 3, 'y'),
(1, 4, 'y'),
...
(43229,43228,'n');
I would like to write a query that, given one photo, lists all photos that match it. If a photo can match at most one other (i.e., a person's face can appear in at most two photos), then I don't need to do a JOIN; the rows of <b>pairs</b> table represents all the pairwise matches. If a person's face can appear at most in 3 photos, I'd only have to do a self join, like this:
Quote

SELECT * FROM `pairs` p1 
CROSS JOIN pairs p2 ON (p1.id1=p2.id1 OR p1.id1=p2.id2) AND p1.match='y' AND p2.match='y'
WHERE p1.id1<>p2.id1 AND p1.id2<>p2.id2
But I don't know how many times a person's face can appear in the photos. Is there a general solution for N? Is there an initial query I can/should run on the <b>pairs</b> table to see how many self-joins I need to do?

Thanks.

Options: ReplyQuote


Subject Written By Posted
Query To Find the Number of Self-Joins? John Kim 05/16/2010 12:30AM
Re: Query To Find the Number of Self-Joins? laptop alias 05/16/2010 05:05AM
Re: Query To Find the Number of Self-Joins? Rick James 05/16/2010 10:47AM
Re: Query To Find the Number of Self-Joins? laptop alias 05/17/2010 01:52AM
Re: Query To Find the Number of Self-Joins? John Kim 05/17/2010 04:06PM
Re: Query To Find the Number of Self-Joins? laptop alias 05/17/2010 04:37PM


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.