MySQL Forums
Forum List  »  Newbie

Re: Query To Find the Number of Self-Joins?
Posted by: Rick James
Date: May 16, 2010 10:47AM

First, lets be clear on what is (and is not) in `pairs`...
Assumption 1: `pairs` has both id1,id2 and id2,id1. This is redundant (twice as many rows as needed), but simplifies the code below. If you avoid the redundancy, the code would need a UNION.
Assumption 2: x,x is not stored in `pairs`. That is you don't have the picture similar to itself.
Assumption 3: Only matches are stored in `pairs`, as laptop recommends.

Those assumptions, which can be enforced as you build `pairs` will make the fetching code simpler.

SELECT s.filename
    FROM pairs p
    JOIN images s  ON p.id2 = s.id
    WHERE p.id1 = ?             -- The id you are matching against

Caveat: GROUP_CONCAT has a 1KB limit.

Options: ReplyQuote


Subject
Written By
Posted
Re: Query To Find the Number of Self-Joins?
May 16, 2010 10:47AM


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.