VB SQL statemtnt to retrieve data description

[new2unix]

Hi,

I have two SQL 7 tables called, "Model," and "ModelMembers."

Model table has data as follow:

ID ModelName
1 Model1
2 Model2
3 Model3
4 Model4
5 Model5
6 Model6

ModelMembers table has data representing association between all models:

ID ModelID ModelMemberModelID
1 1 1
2 1 2
3 4 4
4 3 3
5 4 5
6 5 5

If possible, I need to write a SQL statement to take the data from ModelMembers and query against the Model table to retrieve the ModelName and show the result as follow:

ID ModelID ModelName ModelMemberModelID ModeMemberlModelName
1 1 Model1 1 Model1
2 1 Model1 2 Model2
3 4 Model4 4 Model4
4 3 Model3 3 Model3
5 4 Model4 5 Model5
6 5 Model5 5 Model5

Thanks for any help.

Mike

 

[chiph]

You would join against your Model table twice, using a different table alias each time:

SELECT A.ID, A.ModelID, 
  B.ModelName, A.ModelMemberID,
  C.ModelName
FROM tbl_ModelMembers A
  INNER JOIN tbl_Model B
    ON A.ModelID = B.ID
  INNER JOIN tbl_Model C
    ON A.ModelMemberID = C.ID
WHERE ....

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first