Reporting a table with two columns from another table

[John Bokma]

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/
Happy Customers: http://castleamber.com/testimonials.html

[Stu]

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

[John Bokma]

"Stu" <st************ **@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.

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/
Happy Customers: http://castleamber.com/testimonials.html

[Stu]

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

Nice web pages, BTW.

Stu

[Robert Klemme]

John Bokma wrote:

"Stu" <st************ **@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)

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

robert

[Erland Sommarskog]

John Bokma (jo**@castleamb er.com) writes:

"Stu" <st************ **@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, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx