selection with 2 joins to the same table

[tonvaneck]

Dear sqlserver 2000 users,

We don't know or this is the right forum but maybe there is someone who can help.
We are busy to create a simple select statement. In this select statement there are two joins to one tabel.

TblReaders
Name1_id name2_id
1 2
3 4

TblNames
Id Names
1 John
2 Mary
3 Edward
4 NN

The query must create next result:

Name1 Name2
John Mary
Edward NN

Knows someone a solution?

Thanks for reading.

Ton.

 

[bborissov]

SELECT Name1_id, Names1.Names, Name2_id, Names2.Names
FROM TblReaders
LEFT JOIN TblNames Names1 ON TblReaders.Name1_Id = Names1.Id
LEFT JOIN TblNames Names2 ON TblReaders.Name2_Id = Names2.Id

not tested

Borislav Borissov

 

[tonvaneck]

Hello Borislav,

Thanks for the quick reaction but there is a syntax error (operator is missing in the query expression)

Maybe you can correct it.

Nice regards,

Ton.

 

[bborissov]

I tryed here with my tables and there is no problem with this type of query.

SELECT TblReaders.Name1_id, Names1.Names AS Name1, TblReaders.Name2_id, Names2.Names AS Name2
FROM TblReaders
LEFT JOIN TblNames Names1 ON TblReaders.Name1_Id = Names1.Id
LEFT JOIN TblNames Names2 ON TblReaders.Name2_Id = Names2.Id

If the error persist, try this and tell me what happens:

SELECT TblReaders.Name1_id, Names1.Names AS Name1
FROM TblReaders
LEFT JOIN TblNames Names1 ON TblReaders.Name1_Id = Names1.Id

Borislav Borissov

 

[tonvaneck]

It is working correctly. Thanks a lot for the energy you put in this problem.

Ton.