472,805 Members | 1,507 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,805 software developers and data experts.

Best alternative for an INTERSECT?

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
Jul 20 '05 #1
6 39792
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.
Jul 20 '05 #2
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


Jul 20 '05 #3
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
Jul 20 '05 #4
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.
Jul 20 '05 #5
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
Jul 20 '05 #6
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


Jul 20 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: sunaina | last post by:
I am doing the following query using intersect but gives me an error 'error in sql syntax. I tried using join as well but gives me similar error. In the following code still has intersect just to...
3
by: Brian | last post by:
Hello all, Anyone got links to some examples of using Region and/or Region.Intersect in a graphical / gaming enviroment? Am investigating into using it for hit-lines in my game. Brian
1
by: Brian | last post by:
Greetings all, I wrote a message regarding hit-lines here a couple of days back saying: "Anyone got links to some examples of using Region and/or Region.Intersect in a graphical / gaming...
2
by: ngenius | last post by:
Hello everyone, I am new to MySQL but just realized it does not support INTERSECT in MySQL 5.0 Community Server. I have this problem to solve. (1) cust_table cust_id city_code. ...
1
by: Xpertboy | last post by:
i am trying to execute following code in asp var strSQL1="select Model from Phones where Brand='"+ brand +"'"; var strSQL2="select Model from Phones where Model='"+ model +"'"; var...
3
by: bzb | last post by:
hi ALL, I have a situation to use INTERSECT in my query, but INTERSECT is not supported in "mysql 4.1.15". Can anyone explain me how to tackle this problem..... ! Also, is there...
1
by: maddamon | last post by:
My problem looks like that: SELECT * FROM `table` where `attribute` = '24' INTERSECT SELECT * FROM `table` where `attribute` = '16'; and my 'table' structure is :...
1
by: b00010783 | last post by:
Hi all, I have the table below. +--------+---------+-------------+--------------------------------+-------+----+ | parent | country | city | hotel | stars | id | ...
1
by: =?Utf-8?B?TWl0Y2hX?= | last post by:
Hello, I have the following code: DirectoryInfo pdfDirectory = new DirectoryInfo(@"..\Documents"); var files = from f in pdfDirectory.GetFiles("*.pdf") select new { Filename =...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
0
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
0
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
14
DJRhino1175
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
0
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
0
by: lllomh | last post by:
How does React native implement an English player?
0
by: Mushico | last post by:
How to calculate date of retirement from date of birth
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.