Re: How to search by groups efficiently with MySql 4.0.15?

[Roger Baklund]

* Jouni Hartikainen
> Hi

* someone else
> >A group_id cannot be both 1 AND 3, but it can be  1 OR 3.
> >Try OR

* Jouni Hartikainen
> Yes. I know that group_id can't be both 1 and 3 at the same time. This is
> exactly my problem. How to find a record that belongs to both groups 1
> AND 3? If I use OR here, I get also records that only belong to group 1
> or only belong to group 3. But I don't want these records.
>
> For example:
>
> I have three records A, B and C. A belongs to groups 1 and 2, B
> belongs to group 1 and C belongs to group 2. Now I wan't to find a
> record that belongs to groups 1 AND 2. (So I want that the result for
> my query is only group A) How can I do that? Not with OR I think.
>
> Any ideas? Or do I just have to take everything out from DB and then
> manually loop through all records in my client code?

You have to join your groups twice:

SELECT record.*
  FROM record
  LEFT JOIN link l1 ON
    l1.archive_id = record.archive_id
  LEFT JOIN link l2 ON
    l2.archive_id = record.archive_id
  WHERE
    l1.group_id = 1 AND
    l2.group_id = 2

...or even join in the group names, it will be almost just as fast:

SELECT record.*
  FROM record
  LEFT JOIN link l1 ON
    l1.archive_id = record.archive_id
  LEFT JOIN group g1 ON
    g1.id = l1.group_id
  LEFT JOIN link l2 ON
    l2.archive_id = record.archive_id
  LEFT JOIN group g2 ON
    g2.id = l2.group_id
  WHERE
    g1.name = "A" AND
    g2.name = "B"

--
Roger