Hi
>From: "Roger Baklund" <roger@stripped>
>To: <mysql@stripped>
>
> > 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.
>
>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
Yes. That does the trick. :) Thanks Roger!
Unfortunately when I have a dozen groups the query becomes pretty huge. And
this kind of query is not that easy to build programmatically, but maybe I
just have to live with the fact that there is no easier way to do this. I
have also thought changing the structure of the tables, but haven't come up
with anything usefull yet.
One idea I had is that maybe some kind of bitmap could be used to represent
groups so that I
could then use bitwise operators to do the job. The problem here is that I
have a couple of hundred
groups and none of MySQL's column types with bitwise operators is that wide,
I think.
Another way could be just simply store groups data for a record as some kind
of a comma separated list in the record table. In that way simple text
search could be used to find by groups. Downside of this strategy is that
it's not very efficient neither in speed nor with space consumption. And
maintaining integrity using this kind of table layout would be pain..
Any ideas how I could rearrange the data in a way that I could search it by
groups without this
big join?
Sincerely,
Jouni Hartikainen
kalle_kukkanen@stripped
_________________________________________________________________
Add photos to your e-mail with MSN 8. Get 2 months FREE*.
http://join.msn.com/?page=features/featuredemail
