SQL JOIN question

Dev Shed Forums Sponsor:

January 3rd, 2004, 05:12 PM

dadelcas

Contributing User

Join Date: Jul 2003

Location: Venezuela

Posts: 95

Time spent in forums: 5 m 28 sec
Reputation Power: 6

SQL JOIN question

Hi all...

I have two tables:

*) TableA with AID as primarykey
*) TableB with BID as primarykey and AID as foreignkey

I have BID=X. I want all records on A that have the same AID that the TableB on record X. One way is:

SELECT * FROM TableA INNER JOIN TableB ON TableA.AID=TableB.AID WHERE TableA.AID IN (SELECT AID FROM TableB WHERE BID=X)

But I do not want to use IN as a part of this SQL by optimization reason. Anybody can help me to do it in other way? I was trying using all JOIN forms, but none results (I do not understand all JOIN types yet).

Thanks in advance...

__________________
-DdC

January 3rd, 2004, 05:14 PM

christo

Introspective

Join Date: Nov 2001

Location: London, UK

Posts: 3,297

Time spent in forums: 3 Days 1 h 9 m 17 sec
Reputation Power: 104

Quote:

I have BID=X. I want all records on A that have the same AID that the TableB on record X

makes no sense.. What are you trying to do?

christo

__________________
.
Spiration channels: Free scripts, programming tutorials and articles
Dotcut alerts: Online Press cuttings / news alerts
Clearprop: UK microlight school, wiltshire
Uk dating: UK safe dating with Topdates
About Christo
.
.

January 3rd, 2004, 05:21 PM

dadelcas

Contributing User

Join Date: Jul 2003

Location: Venezuela

Posts: 95

Time spent in forums: 5 m 28 sec
Reputation Power: 6

Yea, you have reason. I wirte wrong table names. The SQL is:

SELECT * FROM TableB INNER JOIN TableA ON TableB.AID=TableA.AID WHERE TableB.AID IN (SELECT AID FROM TableA WHERE AID=X)

I want all childrens of a parent who has a particular children with ID=X

Thanks

January 3rd, 2004, 05:23 PM

dadelcas

Contributing User

Join Date: Jul 2003

Location: Venezuela

Posts: 95

Time spent in forums: 5 m 28 sec
Reputation Power: 6

Sorry again. This is the sql:

SELECT * FROM TableB INNER JOIN TableA ON TableB.AID=TableA.AID WHERE TableB.AID IN (SELECT AID FROM TableB WHERE BID=X)

I don't want to use IN: Instead prefer joins

January 3rd, 2004, 05:27 PM

christo

Introspective

Join Date: Nov 2001

Location: London, UK

Posts: 3,297

Time spent in forums: 3 Days 1 h 9 m 17 sec
Reputation Power: 104

isn't that the same as

Code:

select * from TableB where BID=X

it looks like you're trying to surmise a real-life example and getting it garbled on the way :/

christo

January 3rd, 2004, 05:34 PM

dadelcas

Contributing User

Join Date: Jul 2003

Location: Venezuela

Posts: 95

Time spent in forums: 5 m 28 sec
Reputation Power: 6

Yeah, I can't put the real names. But is not the same as

select * from TableB where BID=X

This returns one record then I want AID of this record. Later, I want all records in TableB with that AID. It is...

Thanks for your soon response

January 3rd, 2004, 05:50 PM

christo

Introspective

Join Date: Nov 2001

Location: London, UK

Posts: 3,297

Time spent in forums: 3 Days 1 h 9 m 17 sec
Reputation Power: 104

If only I could speak Portugese

Quote:

This returns one record then I want AID of this record. Later, I want all records in TableB with that AID. It is...

perhaps an implicit inner join like this would do what you need:

Code:

select * from TableA, TableB where TableA.AID='X' andTableB AID=TableA.AID

christo
(still puzzled)

January 3rd, 2004, 05:53 PM

dadelcas

Contributing User

Join Date: Jul 2003

Location: Venezuela

Posts: 95

Time spent in forums: 5 m 28 sec
Reputation Power: 6

I have BID='X' instead of AID='X'

January 3rd, 2004, 05:57 PM

dadelcas

Contributing User

Join Date: Jul 2003

Location: Venezuela

Posts: 95

Time spent in forums: 5 m 28 sec
Reputation Power: 6

With two querys or with the IN operator it is easy. But I prefer one query if possible

#10

January 3rd, 2004, 06:18 PM

christo

Introspective

Join Date: Nov 2001

Location: London, UK

Posts: 3,297

Time spent in forums: 3 Days 1 h 9 m 17 sec
Reputation Power: 104

lol- how about

Code:

select * from TableA, TableB where TableB.AID='X' and TableB.AID=TableA.AID

christo

#11

January 3rd, 2004, 06:23 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 17,653

Time spent in forums: 1 Month 3 Weeks 6 Days 12 h 42 m 2 sec
Reputation Power: 981

Quote:

I want all childrens of a parent who has a particular children with ID=X

hmmm, that seems pretty clear to me

Code:

select otherchildren.* 
  from TableB X
inner 
  join TableA parent
    on X.AID = parent.AID 
inner
  join TableB otherchildren
    on parent.AID = otherchildren.AID   
 where X.BID = 'X'
   and X.BID <> otherchildren.BID

include or exclude the last line depending on whether you want the original X child included in the result set

__________________
r937.com | rudy.ca

#12

January 3rd, 2004, 06:44 PM

dadelcas

Contributing User

Join Date: Jul 2003

Location: Venezuela

Posts: 95

Time spent in forums: 5 m 28 sec
Reputation Power: 6

Quote:

Originally posted by christo
lol- how about

Code:

select * from TableA, TableB where TableB.AID='X' and TableB.AID=TableA.AID

christo

This returns the same as

SELECT * FROM TableB WHERE TableB.AID='X'

Quote:

hmmm, that seems pretty clear to me...

There are only two tables: TableA and TableB

#13

January 3rd, 2004, 06:46 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 17,653

Time spent in forums: 1 Month 3 Weeks 6 Days 12 h 42 m 2 sec
Reputation Power: 981

DdC, although my query looks like it's querying three tables, there are only two tables involved

please try my query

thank you

#14

January 3rd, 2004, 06:50 PM

dadelcas

Contributing User

Join Date: Jul 2003

Location: Venezuela

Posts: 95

Time spent in forums: 5 m 28 sec
Reputation Power: 6

See this php script-peice:

PHP Code:


		
			
 $sql = 'SELECT AID FROM TableB WHERE BID="X"';

$query = mysql_query($sql);

$row = mysql_fetch_row($query);

$AID = $row[0];

mysq_free_result($query);



$sql = "SELECT * FROM TableB WHERE AID='$AID'";

$query = mysql_query($sql);

$row = mysql_fetch_row($query);

//DO SOMETHING WITH RESULTS

mysq_free_result($query);

I want to merge the 2 querys just in only one...

Thanks