Best alternative for an INTERSECT?

[Nick]

I have three tables

books (id, title, author)
authors (id, name)
authors_groups (author_id, group_id)

I have a query that searches for authors who must be in a least one
subgroup of two groups. I hope that makes sense. Here are some
examples im working on...

SELECT * FROM authors, authors_groups
WHERE authors.id = authors_groups.author_id
AND authors_groups.group_id IN (1,2,3,4)
INTERSECT
SELECT * FROM authors
WHERE authors.id = authors_groups.author_id
AND authors_groups.group_id IN (3,4,5,6)

This would return authors who are both in groups 3,4 but MySQL doesnt
support INTERSECT statement, so I tried this...

SELECT * FROM authors
WHERE authors.id IN
(SELECT author_id FROM authors_groups
WHERE group_id IN (1,2,3,4))
AND authors.id IN
(SELECT author_id FROM authors_groups
WHERE group_id IN (3,4,5,6))

This however returns all authors in all groups (instead of just 3,4).
However, if I use only one subquery and use literal values in the
second WHERE clause it works. ???

Anyone have some suggestions on how I can make this work. Thanks!
-Nick

[Bill Karwin]

Nick wrote:

I have a query that searches for authors who must be in a least one
subgroup of two groups. I hope that makes sense. Here are some
examples im working on...

SELECT * FROM authors, authors_groups
WHERE authors.id = authors_groups.author_id
AND authors_groups.group_id IN (1,2,3,4)
INTERSECT
SELECT * FROM authors
WHERE authors.id = authors_groups.author_id
AND authors_groups.group_id IN (3,4,5,6)

How about this:

SELECT *
FROM authors A INNER JOIN authors_groups G ON A.id = G.author_id
WHERE G.group_id IN (1,2,3,4)
AND G.group_id IN (3,4,5,6)

INTERSECT should be used when the two queries return columns of
compatible datatypes, but query from two separate tables.
See the example at http://www.1keydata.com/sql/sql-intersect.html

In your case, you're querying the same tables in both parts of the
INTERSECT (I assume it was a typo when you omitted authors_groups from
the FROM clause above), so you should be able to move the logic into a
simple AND in the WHERE clause.

Regards,
Bill K.

[Isaac Blank]

Try this:

SELECT * FROM authors
WHERE authors.id IN
(SELECT author_id FROM authors_groups
WHERE group_id IN (1,2,3,4)
AND group_id in (3,4,5,6)
)
"Nick" <nb********@hotmail.com> wrote in message
news:ce**************************@posting.google.c om...

I have three tables

books (id, title, author)
authors (id, name)
authors_groups (author_id, group_id)

I have a query that searches for authors who must be in a least one
subgroup of two groups. I hope that makes sense. Here are some
examples im working on...

SELECT * FROM authors, authors_groups
WHERE authors.id = authors_groups.author_id
AND authors_groups.group_id IN (1,2,3,4)
INTERSECT
SELECT * FROM authors
WHERE authors.id = authors_groups.author_id
AND authors_groups.group_id IN (3,4,5,6)

This would return authors who are both in groups 3,4 but MySQL doesnt
support INTERSECT statement, so I tried this...

SELECT * FROM authors
WHERE authors.id IN
(SELECT author_id FROM authors_groups
WHERE group_id IN (1,2,3,4))
AND authors.id IN
(SELECT author_id FROM authors_groups
WHERE group_id IN (3,4,5,6))

This however returns all authors in all groups (instead of just 3,4).
However, if I use only one subquery and use literal values in the
second WHERE clause it works. ???

Anyone have some suggestions on how I can make this work. Thanks!
-Nick

[Nick]

Thank you Bill very much for your suggestion. It does not, however,
return any results. The authors_groups table has more than one
authors_groups.group_id listed for each authors_groups.author_id.
Could this be why? I found someone that suggested I try using...

SELECT *
FROM authors A, authors_groups B, authors_groups C
WHERE B.group_id IN (1,2,3,4)
AND A.id = B.author_id
AND C.group_id IN (3,4,5,6)
AND A.id = C.author_id

It works but the statement gets huge as mutiple groups get added to
the search list. Any suggestions on why I can't get yours to work?

-Nick

[Bill Karwin]

Nick wrote:

Thank you Bill very much for your suggestion. It does not, however,
return any results. The authors_groups table has more than one
authors_groups.group_id listed for each authors_groups.author_id.
Could this be why? I found someone that suggested I try using...

SELECT *
FROM authors A, authors_groups B, authors_groups C
WHERE B.group_id IN (1,2,3,4)
AND A.id = B.author_id
AND C.group_id IN (3,4,5,6)
AND A.id = C.author_id

My solution works, I tried it. If it doesn't, then there is something
about your requirements that I have not understood. Here is what I
tried in my test database:

CREATE TABLE authors (id int(11) NOT NULL);
INSERT INTO authors VALUES (1),(2),(3),(4),(5),(6);

CREATE TABLE authors_groups (author_id int(11) NOT NULL, group_id
int(11) NOT NULL);
INSERT INTO authors_groups VALUES
(1,1),(1,2),(2,2),(2,3),(3,3),(3,4),(4,4),(4,5),(5 ,5),(5,6);

# Here's my solution:
select * from authors a, authors_groups b where a.id = b.author_id and
b.group_id in (1,2,3,4) and b.group_id in (3,4,5,6);
+----+-----------+----------+
| id | author_id | group_id |
+----+-----------+----------+
| 2 | 2 | 3 |
| 3 | 3 | 3 |
| 3 | 3 | 4 |
| 4 | 4 | 4 |
+----+-----------+----------+

# Here's the other solution that someone gave you:
select * from authors a, authors_groups b, authors_groups c where a.id =
b.author_id and a.id = c.author_id and b.group_id in (1,2,3,4) and
c.group_id in (3,4,5,6);
+----+-----------+----------+-----------+----------+
| id | author_id | group_id | author_id | group_id |
+----+-----------+----------+-----------+----------+
| 2 | 2 | 2 | 2 | 3 |
| 2 | 2 | 3 | 2 | 3 |
| 3 | 3 | 3 | 3 | 3 |
| 3 | 3 | 4 | 3 | 3 |
| 3 | 3 | 3 | 3 | 4 |
| 3 | 3 | 4 | 3 | 4 |
| 4 | 4 | 4 | 4 | 4 |
| 4 | 4 | 4 | 4 | 5 |
+----+-----------+----------+-----------+----------+

The two solutions give the same set of distinct author id's.

Regards,
Bill K.

[Nick]

Bill, youre totally right. I should of articulated what im trying to
do a little better. What I am trying to do is search for authors who
are in at least one subgroup of multiple (search) groups. For
example...

If authors_groups table looks like this...

+-----------+----------+
| author_id | group_id |
+-----------+----------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 3 |
+-----------+----------+

I want to get a list of author_id's that are in at least one of
group_id's (1,3) and are also in at least one of groups (2,4). My
desired query result would return only author_id 1 and would not
return author_id 2 because author_id 2 is not apart of groups (2,4).
The reason I was not getting any results with your query is because I
was retarded and did not test with the example I gave to you. Instead
I was testing...

SELECT *
FROM authors A INNER JOIN authors_groups G ON A.id = G.author_id
WHERE G.group_id IN (1,3)
AND G.group_id IN (2,4)

which returns no results. -Nick

[Isaac Blank]

If that's what you're after, then one of your original solutions should
work:

SELECT * FROM authors
WHERE authors.id IN
(SELECT author_id FROM authors_groups
WHERE group_id IN (1,3))
AND authors.id IN
(SELECT author_id FROM authors_groups
WHERE group_id IN (2,4))
"Nick" <nb********@hotmail.com> wrote in message
news:ce**************************@posting.google.c om...

Bill, youre totally right. I should of articulated what im trying to
do a little better. What I am trying to do is search for authors who
are in at least one subgroup of multiple (search) groups. For
example...

If authors_groups table looks like this...

+-----------+----------+
| author_id | group_id |
+-----------+----------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 3 |
+-----------+----------+

I want to get a list of author_id's that are in at least one of
group_id's (1,3) and are also in at least one of groups (2,4). My
desired query result would return only author_id 1 and would not
return author_id 2 because author_id 2 is not apart of groups (2,4).
The reason I was not getting any results with your query is because I
was retarded and did not test with the example I gave to you. Instead
I was testing...

SELECT *
FROM authors A INNER JOIN authors_groups G ON A.id = G.author_id
WHERE G.group_id IN (1,3)
AND G.group_id IN (2,4)

which returns no results. -Nick