Posted by Erland Sommarskog on 03/22/06 00:36

John Bokma (john@castleamber.com) writes:
> "Stu" <stuart.ainsworth@gmail.com> wrote:
>> 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
>
> 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.

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
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]