July 11, 2005 at 2:04 pm
i have 2 tables with some of teh same field names/types but soem fields are only in one table or the other.
one of the common fields between the two tables is collectionID.
so what i want to do is write a query that will return in one resultset, all the feilds from each of the 2 tables where collectionID=2 for example.
is this possible?
for example table 1:
ID, CollectionID, Department, DocNum, Title, URL, DateAdded, Description
Table2:
ID, CollectionID, Dept, DocumentName, Document, LastModified, ModifiedBy, OptionalField1, OptionalField2, OptionalField3, OptionalField4, OptionalField5
July 11, 2005 at 2:23 pm
select
T1.[ID], T1.CollectionID, T1.Department, T1.DocNum, T1.Title, T1.URL, T1.DateAdded, T1.[Description],
T2.[ID], T2.CollectionID, T2.Dept, T2.DocumentName, T2.Document, T2.LastModified, T2.ModifiedBy,
T2.OptionalField1, T2.OptionalField2, T2.OptionalField3, T2.OptionalField4, T2.OptionalField5
from table1 T1 inner join table2 T2 on T1.CollectionID = T2.CollectionID
where T1.CollectionID = 2
is that what u need r did i get it wrong?
July 11, 2005 at 2:26 pm
please c my updated post. actually thr was no need to declare a variable
July 11, 2005 at 2:26 pm
yeah that might do it - lemme try.
July 12, 2005 at 6:59 am
If the two tables contain different sets of rows, you might want to do a FULL JOIN to get all rows instead of an INNER JOIN. And you can combine the common fields with ISNULL (or COALESCE for you purists).
SELECT isnull(a.ID, b.ID) as ID, -- COMMON FIELDS
isnull(a.CollectionID, b.CollectionID) as CollectionID,
isnull(a.Department, b.Dept) as Department,
a.DocNum, b.Document, b.DocumentName, -- UNIQUE FIELDS
a.Title, a.URL, a.DateAdded, a.Description,
b.LastModified, b.ModifiedBy, ...
FROM Table1 a
FULL JOIN Table2 b ON a.ID = b.ID
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply