Returning data related to two different rows in same table.

160 100+

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

Subscribe Post Reply

2824

code green

1,726

Expert 1GB

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

r035198x

13,262

8TB

...
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

 select bID from MapTable where aID = aIDOne

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

 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

 select aID from MapTable where bID in (

select bID from MapTable where aID = aIDOne

and bID in (select bID from MapTable where aID = aIDTwo))

 and aID not in (aIDOne, aIDTwo)

Oct 1 '07 #3

bugboy

160

100+

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

1,726

Expert 1GB

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

by: Rowland Hills | last post by:

I have a table which is returning inconsistent results when I query it! In query analyzer: If I do "SELECT * FROM TABLE_NAME" I get no rows returned. If I do "SELECT COL1, COL2 FROM...

Microsoft SQL Server

Combining data from differnet Excel sheets

by: Ilan | last post by:

Hi all I need to add data from two Excel sheets (both on the same workbook) to an existing table in my SQL DB. The problem is that each sheet holds different fields for the same record, though...

Microsoft SQL Server

Best way or returning hierarchical data

by: Daisy | last post by:

Let's say I've got a forum, where users can be moderators of each forum. Tables look like this: USER -------- user_key name FORUM

C# / C Sharp

Returning complex data relationships from a stored procedure.

by: Peter Rilling | last post by:

To minimize database calls, I would like to have a single stored procedure return all the data necessary to display on my page. This information is related from several different tables and what I...

C# / C Sharp

Looping through related data..?

by: Jim in Arizona | last post by:

Usually, If i need special formatting, I don't use the datagrid control and use a loop that processes a table for each record read from the database (as in classic asp) like so: ...

ASP.NET

Data Storage Suggestions

by: Brian P. Hammer | last post by:

I have data from multiple SQL tables, some of the tables will only have one row, while others multiple rows. I load a bunch of data from the various tables and add them to a third party grid. With...

.NET Framework

Handling data relations in Gridview

by: sutphinwb | last post by:

Hi - This could be a simple question. When I relate two tables in a datasetet, how do I get that relation to show up in a GridView? The only way I've done it, is to create a separate table in the...

ASP.NET

SqlDataAdapter returning empty rowsets that shouldn't be?

by: downwitch | last post by:

Hi, I'm having trouble getting data in ADO.Net recordsets. (VS 2005, SQL Server 2005). All my rowsets come back empty--whether sourced from queries or stored procs--despite ODBC connections with...

Visual Basic .NET

Returning a DataTable blocks WCF-Service

by: Frank Hauptlorenz | last post by:

Hello, I recognized some days ago, that returning a DataTable blocks my WCF-Service. Is this a known bug? If I add this table to a new DataSet() and return this, it works. Thank you, Frank

.NET Framework

Easy Steps to Fix "Canon Printer Won't Connect to WiFi Network"

by: taylorcarr | last post by:

A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...

General

Basic Javascript concepts

by: aa123db | last post by:

Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...

Javascript

Batch import of multiple excel files into the database

by: ryjfgjl | last post by:

If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...

Data Management

Merging data from multiple Excel files

by: ryjfgjl | last post by:

In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...

Data Management

Migrating Website to Cloud - Emmanuel Katto

by: emmanuelkatto | last post by:

Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel

General

Navigating the Data Structures and Algorithms (DSA)

by: BarryA | last post by:

What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...

Algorithms / Advanced Math

Looking to do Android software development, any suggestions? Is flutter better?

by: nemocccc | last post by:

hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

General

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...

General

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

C / C++

Returning data related to two different rows in same table.

Similar topics