By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,856 Members | 2,164 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,856 IT Pros & Developers. It's quick & easy.

Reporting a table with two columns from another table

P: n/a
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
Mar 20 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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

Mar 20 '06 #2

P: n/a
"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
Mar 21 '06 #3

P: n/a
Stu
Again, without DDL (data Definition Language; Create Table scripts) and
some sample data, it's tough to help...

Nice web pages, BTW.

Stu

Mar 21 '06 #4

P: n/a
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
Mar 21 '06 #5

P: n/a
John Bokma (jo**@castleamber.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****@sommarskog.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
Mar 21 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.