By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
462,185 Members | 672 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 462,185 IT Pros & Developers. It's quick & easy.

Returning data related to two different rows in same table.

bugboy
100+
P: 160
I have a foreign key table for defining many to many relationships in two other tables.
This table (MapTable) has two foreign keys, aID and bID.

MapTable
aID | bID


I start the query knowing 2 different aIDs that are related in different rows to many different bIDs. I want to find which bIDs they have in common.. Only the ones they have in common.

And then hopefully in the same query (or subquery) i want to find all the aIDs ( minus the two i started with) that also match the found bIDs. All the aIDs returned must match every bID found.

...arrgg it sounds so simple.

I've done a lot of reading and experimenting with no success.... Can anybody point me in the right direction? I'll post my attempts if i get close but after two days i don't have much to show :p

Thanks!
BugBoy
Sep 29 '07 #1
Share this Question
Share on Google+
4 Replies

code green
Expert 100+
P: 1,726
I have a similar table that is used to provide the compatibility between products.
Couldn't be done cleanly in one query and had to settle on two seperate queries.
query 1. select bIDs that are linked to aIDs
query 2. select aIDs that are linked to bIDs
Is this what you are trying to do?
Oct 1 '07 #2

10K+
P: 13,262
...
I start the query knowing 2 different aIDs that are related in different rows to many different bIDs. I want to find which bIDs they have in common.. Only the ones they have in common.

..
That is
Expand|Select|Wrap|Line Numbers
  1. select bID from MapTable where aID = aIDOne
  2. and bID in (select bID from MapTable where aID = aIDTwo)




...

And then hopefully in the same query (or subquery) i want to find all the aIDs ( minus the two i started with) that also match the found bIDs. All the aIDs returned must match every bID found.

...
That is
Expand|Select|Wrap|Line Numbers
  1. select aID from MapTable where bID in (PlugInThatQueryAboveHere) and aID not in (aIDOne, aIDTwo)
So in the end the query would look like


Expand|Select|Wrap|Line Numbers
  1. select aID from MapTable where bID in (
  2. select bID from MapTable where aID = aIDOne
  3. and bID in (select bID from MapTable where aID = aIDTwo))
  4.  and aID not in (aIDOne, aIDTwo)
Oct 1 '07 #3

bugboy
100+
P: 160
Thanks r035198x, It works! I'm just having trouble with the indexes now. The first query is using type 'INDEX' and it reads every row (according to EXPLAIN). That makes it really slow since i have several million rows. I'm going to attempt it with a combination of joins... once i figure them out that is...

code green: yes that's basically it. I'm having problems with my index usage though. Subqueries seem to be too slow for my large dataset. What did you end up doing?
Oct 11 '07 #4

code green
Expert 100+
P: 1,726
code green: What did you end up doing?
To be honest, I gave up and conducted two competely seperate select queries,
then combined the data in PHP code.
Subqueries seem to be too slow for my large dataset.
Sub-queries are notoriously slow.
Rumour has it that they can all be re-written as JOINS,
but without INDEXES in the table you will still have performance problems.
I am working on a mssql database at the moment where simple JOINs were so slow,
I rewrote the PHP script to perform one SELECT then perform multiple SELECTs within a loop.
This reduced a script execution from 5 mins to 30secs (using PEAR benchmark functions)
Oct 12 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.