Query to return multiple foriengn keys from same table

[IncEzed]

I have an SQL database with the following:
[B]TableA[/B] with ItemID, Fk1, Fk2, Fk3, Fk4
[B]TableB [/B] with DescID, description -
Where Fk1-4 is equal to a DescID in Table B.

Can I wrie a query that returns everything in one row like this?
ItemID, Fk1, description, Fk2, description, Fk3, description....

[Hugo Kornelis]

Hi IncEzed,

Yes, you can:

SELECT a.ItemID,
a.Fk1, b1.description,
a.Fk2, b2.description,
a.Fk3, b3.description,
a.Fk4, b4.description
FROM TableA AS a
INNER JOIN TableB AS b1
ON b1.DescID = a.Fk1
INNER JOIN TableB AS b2
ON b2.DescID = a.Fk2
INNER JOIN TableB AS b3
ON b3.DescID = a.Fk3
INNER JOIN TableB AS b4
ON b4.DescID = a.Fk4;

I'm not very happy with the table design, though. These four Fk columns
look suspiciously like a repeating group.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis