Hardous select ?

[TorF]

Hi,

I'm using Access, here's my question !

I have 2 tables calls and connections as :

* calls:
id id_connection_caller id_connection_called
0 0 1
1 0 2
2 2 0

* connections
id address
0 700
1 701
2 702
3 703

I want to make a select that's give me for each call the caller address and the called address as for this example :
- calls_select
id_call address_caller address_called
0 700 701
1 700 702
2 702 700

My problem is that i cannot get fields from 2 connections rows in a single result row with a request as :

SELECT calls.*
FROM connections RIGHT JOIN calls ON (connections.ID = Calls.id_connection_called) AND (Connections.ID = Calls.id_connection_caller);

Can anyone help me ?

Thanks

Guillaume Fillol
Development

 

[TorF]

Hum sorry for my stupid question... I found the solution myself with a request like this :

SELECT c.id, c1.address, c2.address
FROM
(calls c INNER JOIN connections c1 ON (c1.id = c.id_connection_caller))
INNER JOIN connections c2 ON (c2.id = c.id_connection_called);

 

[tlbroadbent]

I have 2 recommendations.

1) Post Access questions in an Access forum such as the following.

forum701 - Microsoft: Access Queries and JET SQL

2) Join Tek-Tips. You can be notified when someone posts a reply to your question and you can easily find your own posts. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.