List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:August 17 1999 1:23pm
Subject:Re: complicated query
View as plain text  
On Tue, 1999-08-17 09:41:27 +0200, Randy Gerritse wrote:
> I am building a dating website with mysql and php3 and run into a real
> complicated query. Normally i would solve this with a number of subselects
> but since these are not yet supported... =(
> Here's the situation, i have the following tables:
> Member (id int pk,username text)
> datingtags (id int pk,name text,description text)
> datingwanttags (id int pk,memberid int,tagid int,tagvalue text)
> datinghavetags (id int pk,memberid int,tagid int,tagvalue text)
> Now i want a query that returns all the people that match the description
> the user is looking for AND who's own search matches the current user.
> Normally i would do it like this:
> function getUserWantTags($userid,$ident) {
[... some PHP code ...]

Randy, next time please state clearly the programming language used.
Not everybody knows every language, and in this case one might have
though of Perl, too, ...

Even more, I think it's much easier for us to help you, if you only
show the pure SQL queries without any programming language corset.

Okay, so you first get a list of all datingwanttags of a user:
  SELECT id, tagid, tagvalue
  FROM   datingwanttags
  WHERE  userid=4711;

And then you construct this kind of query using sub-selects:
  SELECT id, username
  FROM   member
  WHERE  id IN ( SELECT memberid
                 FROM   datinghavetags
                 WHERE  tagid='tagid1' AND tagvalue='value1' )
    AND  id IN ( SELECT memberid
                 FROM   datinghavetags
                 WHERE  tagid='tagid2' AND tagvalue='value2' )
    AND  ...
    AND  id IN ( SELECT memberid
                 FROM   datinghavetags
                 WHERE  tagid='tagn' AND tagvalue='valuen' )

(The tagids should be numbers without quotes, but it's easier to read
this way ...)

That means, you're looking for all ids that match every tag/value

I think, this should work to do the job:
 a) SELECT COUNT(*) AS num FROM datingwanttags WHERE userid=4711;
      --> store this value ($num)

 b) SELECT h.memberid
    FROM   datinghavetags AS h
         , datingwanttags AS w
    WHERE  h.tagid    = w.tagid
      AND  h.tagvalue = w.tagvalue
    GROUP BY h.memberid
    HAVING COUNT(*)=$num;
      --> store resulting list of memberids

    This tries to match every tag/value pair of datinghavetags with
    the corresponding tag/value pair of datingwanttags, groups the
    joined results by memberid, and with HAVING we then select only
    such records, that match often enough (i.e. where alle tag/value
    pairs match).

 c) Though in (b) you could do another join with the Member table,
    to get the username, is think it's better performancewise to do
    this in a seperate step:

    SELECT id, username FROM Member WHERE id IN (list of memberids);

I hope, what I wrote is correct and helpful ...

Martin Ramsch <m.ramsch@stripped> <URL: >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
complicated queryRandy Gerritse17 Aug
  • Re: complicated queryMartin Ramsch17 Aug
    • Re: complicated queryMartin Ramsch17 Aug
      • RE: complicated queryRandy Gerritse17 Aug