Complex Query?

I have 2 tables, say table1, and table2. There is a DocID (primary key) in table1. In table2, DocID is the foriegn key. There can be more than 1 DocID.

this is the table structure (sample)

Table 1:
DocID DocName OtherID etc
1 test 2
2 test2 3

Table2:
TblID DocID OtherID
1 1 10
2 1 13
3 1 25

how do I join these two tables, such that I get all the otherID's for each DocID.
ie.,
DocID OtherID
1 2 and 10 and 13 and 25
2 3

i am writing this query to display search results on a search page (with keyword search) and so, if I display the result in more than one row, then the user might think that there is more than document...whereas the case is that there is only one document with more than one other ID's.

is there any way I can do this? display...more than 1otherID in the same row for the same DociD?
Currently, I am using a left outer join of table1 and table2.

An suggestions on how to do this?Your best bet is probably to do this on the front end rather than in your SQL statement.

Terri|||I agree. Return your resultset in multiple rows, then figure out some way of displaying the results that clearly indicates the relationship you are looking to capture (either flatten into one row, or display as parent/child relationship).|||Use a udf, here is an example:

--sample table1 data
create table t1 (DocID int, DocName varchar(20), OtherID int)
insert into t1
select 1, 'test', 2 union all
select 2, 'test2', 3
--sample table2 data
create table t2 ( TblID int, DocID int, OtherID int)
insert into t2
select 1, 1, 10 union all
select 2, 1, 13 union all
select 3, 1, 25
go
--create our user-defined function (udf)
create function dbo.ConcatOtherIDs(@.DocID int, @.OtherID int)
returns varchar(8000)
as
begin
DECLARE @.String varchar(8000)
SET @.String = CAST(@.OtherID as varchar(10)) + ' and '
SELECT @.String = @.String + CAST(OtherID as varchar(10)) + ' and '
FROM t2
WHERE DocID = @.DocID
RETURN left(@.String,LEN(@.String)-4)
end
go
--test our udf
select docid, dbo.ConcatOtherIDs(DocID,OtherID) OtherIDs
from t1
--clean up test
drop table t1,t2
drop function dbo.ConcatOtherIDs

|||A UDF in this case would be a performance hit since the function would have to be evaluated for every row in the returned result set. prbly the front end is better.

hth|||>>prbly the front end is better.

Unless I am missing some slick way to perform this on the client (read set-based processing in c# or vb.net) you will also require an iteration over the result set. Plus you will be returning a larger result set to the client for further processing.

Might be interesting to see which would be more performant in this case - I bet the udf wins the race every time here though :)|||I am assuming that DocID is a PK in Table1 and thus unique. This UNION should give you what you want with a structure identical to Table1:

SELECT
DocID,
DocName,
OtherID
FROM
Table1
UNION
SELECT
Table2.DocID,
Table1.DocName,
Table2.OtherID
FROM
Table2
INNER JOIN Table1 ON (Table2.DocID = Table1.DocID)
ORDER BY
DocID, OtherID

With your input data I get these results:

DocID DocName OtherID
---- ------ ----
1 test 2
1 test 10
1 test 13
1 test 25
2 test2 3

(5 row(s) affected)

Best Regards,

JayAchTee