SQL Query problem

[BiggerBrother]

I have two tables. The first contains name and id number. The second contains ID1, ID2 and relationships.

I need to iterate through the second table to find when either ID1 or ID2 equal an ID. When this is true, the sataset reurned should contain the other persons ID, their name and their relationship. Confused? Here is my code.

"SELECT Name, Relationship, ID " _
& "FROM Table1, Table2 WHERE id=any
(SELECT ID1 FROM Relationship WHERE ID2 = 1)"
& "or id=any (SELECT ID2 FROM Relationship WHERE ID1 = 1)"

When i run this code, the relationships returned are correct, but the name reutrned is that of the first dataset row. What am i doing wrong? Any help would be fantastic.

BB

 

[boriska40]

Hi,

Try

SELECT Table1.Name, Table2.Relationship, Table1.ID
FROM Table1, Table2
WHERE Table2.ID1 = Table1.ID OR Table2.ID2 = Table1.ID

 

[bvwatson]

I think you want to use a UNION operation on 2 queries.

Q1: get all the people who are ID1 where ID2 = "myID"

Q2: get all the people who are ID2 where ID1 = "myID"

Thus:

select T1.name, T2.relationship
from T1 join T2 on T1.id = T2.id1
where T2.id2 = ?
UNION
select T1.name, T2.relationship
from T1 join T2 on T1.id = T2.id2
where T2.id1 = ?

Given T1:
Name, ID: Bob, 1
Name, ID: Mary, 2
Name, ID: Joe, 3
Name, ID: Fred, 4

And T2:
ID1, ID2, Relation: 1, 2, Spouse
ID1, ID2, Relation: 3, 1, Co-worker
ID1, ID2, Relation: 2, 4, Sibling

If ? in the query = 2, you get:
Bob, Spouse
Fred, Sibling

If ? in the query = 1, you get:
Joe, Co-worker
Mary, Spouse

 

[swampBoogie]

SELECT tt1.Name, Relationship, tt2.name
  FROM Table1 tt1, table1 tt2, Table2 
  WHERE tt1.id = 1
    and tt1.id = table2.id1
    and table2.id2 = tt2.id