* 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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
