It would help if you posted DDL and some sample data, but it sounds
like you simply need to do a dual join on your second table using an
alias, eg:

SELECT a.*, b1.*, b2.*
FROM tableA a JOIN TableB b1 ON a.ID = b1.ID
JOIN TableB b2 ON aID2 = b2.ID

HTH,
Stu

Again, without DDL (data Definition Language; Create Table scripts) and
some sample data, it's tough to help...

Nice web pages, BTW.

Stu

I have a table A, with two ID columns. In a report both ID colums should
be shown with the actual value stored in a second table, B

The problem is, both IDs need to be looked up in B, but are not in the
same row.

How do I do this in an efficient way? A sub select?

Thanks,

--
John MexIT: http://johnbokma.com/mexit/
personal page: http://johnbokma.com/
Experienced programmer available: http://castleamber.com/

[quoted text, click to view]

Maybe you joined the second table only once with a criterion on both id
rows and thus got far less results. But that would be wrong if your
description of the problem was right.

Regards

[quoted text, click to view]

which gives me way too many rows :-) (or I did something very stupid)

I want for each result in a a single row, with each ID (and ID2) in TableB
resolved to an actual value.

SELECT ..., ( SELECT b.value FROM b WHERE a.ID = b.ID )
FROM a, b
WHERE a.ID2 = b.ID


--
John MexIT: http://johnbokma.com/mexit/
personal page: http://johnbokma.com/
Experienced programmer available: http://castleamber.com/

John Bokma (john@castleamber.com) writes:
[quoted text, click to view]

Stu made the assumption that an ID would map to exactly one row in
TableB. Maybe that it is not the case, but how could we know that?

There is a standard recommendation for this kind of questions, and
that is that you post:

o CREATE TABLE statements for your tables.
o INSERT statements with sample data.
o Desired result given the sample.

This makes it easy to copy and paste into a query tool to develop a
*tested* solution. Without that information, all you will get is
guesses.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at