Hi
From: "Roger Baklund" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>
> 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 [EMAIL PROTECTED]
_________________________________________________________________
Add photos to your e-mail with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
