February 16, 2006 at 2:18 am
This is more about SQL syntax than SQL Server, but I'm trying to do it in SQL Server 2000, so it applies.
I have 2 tables linked together twice, both times on the identifier of the first table, and I'm trying to access an information in the first table through both links, as different results of the same query ... how do I do that ?
More clearly :
Table1 (ID,targetInformation,other things)
Table2(ID, IDtable1-1, IDtable1-2)
IDtable1-1 = ID
and
IDtable1-2 = ID are the 2 links.
I want "targetInformation" from both links ... all in one query.
I tried "SELECT table1.targetInformation AS target1, table1.targetInformation AS target2 FROM table1, table2 WHERE table1.ID = table2.IDtable1-1 AND table1.ID = table2.IDtable1-2", but it's not erroneous (I can see why, I just can't see how to do it correctly).
February 16, 2006 at 5:05 am
Something like this?
SELECT Table1ID1.targetInformation AS target1,Table1ID2.targetInformation AS target2
FROM Table2 LEFT JOIN Table1 Table1ID1 ON table2.ID1=Table1ID1.ID /*fill with NULL if not present in table1*/
LEFT JOIN Table1 Table1ID2 ON table2.ID2=Table1ID2.ID
ORDER BY Table2.ID
February 16, 2006 at 10:06 am
Try this:
declare @Table1 table (ColumnID int,ColumnDescription varchar(20))
insert @Table1 values(1, 'Description')
insert @Table1 values(2, 'Age')
insert @Table1 values(3, 'Type')
declare @Table2 table (StatementID int, LeftColumnID int, RightColumnID int)
insert @Table2 values(1, 1, 2)
insert @Table2 values(2, 1, 3)
insert @Table2 values(3, 3, 2)
select t2.StatementID, l.ColumnDescription, r.ColumnDescription
from @Table2 t2
inner join @Table1 l
on t2.LeftColumnID = l.ColumnID
inner join @Table1 r
on t2.RightColumnID = r.ColumnID
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply