SQL query question 1

[sweimh]

Not very experience at this yet, so maybe this is a bad query idea from start up..

I have this fields in 2 tables
Table A: myid, name, associateID1, associateID2
Table B: assocateID, name, tel, fax

And I want to make a query, by supplying myid, to find TableA.name, and TableB.name for both TableA.associateID1 and TableA.associateID2.

I can't get around of using TableB twice to find information for associateID1 and associateID2.

anyone can help or point a way? thanks.

 

[swampBoogie]

[kod]
select b1.name as assoc1 , b2.name as assoc2
from tableB b1 join tableA on
b1.associateID = associateID1 join tableB b2 on
associateID2 = b2.associateID
[/kod]

 

[sweimh]

hi swampBoogie,

thx for the pointer. I got it to work.. almost. I probably got lost in ur code somewhere. I'm getting the result I want, but the same result is showing up in multiple times (= number of total recs in Associdv). Can you help me a little bit more with this? thank again.. my code so far:

SELECT b1.nameof AS NameA, b2.nameof AS NameB, A.myName
FROM Associdv AS b, applicant AS A
LEFT JOIN Associdv AS b1 ON b1.id = A.AssoA
LEFT JOIN Associdv AS b2 ON b2.id = A.AssoB
WHERE A.myID = someid

 

[swampBoogie]

SELECT b1.nameof AS NameA, b2.nameof AS NameB, A.myName
FROM applicant AS A
LEFT  JOIN Associdv AS b1 ON b1.id = A.AssoA
LEFT  JOIN Associdv AS b2 ON b2.id = A.AssoB
WHERE A.myID = someid

 

[sweimh]

hmm.. well, stupid me..
I added a select "distinct" ....
and it solves the multiple result problem.

but still, am i actually doing this correctly?

 

[sweimh]

ah, didn't see u reply already.

that was it. thanks so much.

 

[xutopia]

a star to swamp. While answering this question you helped me with my problem!

Gary

http://www.xutopia.com

Haran
********************************