Fill data

INSERT INTO Table1(Title) VALUES('Somedata1')
INSERT INTO Table1(Title) VALUES('Somedata2')
INSERT INTO Table2(ParentId, Table1Id, Type, Title) VALUES(0,1,100,'Somedata
')
INSERT INTO Table2(ParentId, Table1Id, Type, Title) VALUES(1,1,100,'Somedata
')
INSERT INTO Table2(ParentId, Table1Id, Type, Title) VALUES(1,1,100,'Somedata
')
INSERT INTO Table2(ParentId, Table1Id, Type, Title) VALUES(0,1,100,'Somedata
')
INSERT INTO Table2(ParentId, Table1Id, Type, Title) VALUES(0,2,200,'Somedata
')
INSERT INTO Table2(ParentId, Table1Id, Type, Title) VALUES(0,2,100,'Somedata
')
> For each Table1.Id I want to Select Top 1 Table2.Id
> where Table2.Table1Id = Table.Id And Table2.ParentId = 0
> Order By Table2.Type Desc och Table2.Id Desc.
SELECT t2.Id as Table2ID
FROM Table1 t1
INNER JOIN Table2 t2 ON t2.Table1Id=t1.Id AND t2.ParentId=0
AND t2.Id=(SELECT TOP 1 t3.Id
FROM Table2 t3
WHERE t3.Table1Id=t1.Id AND
t3.ParentId=0
ORDER BY t3.Type DESC,Id Desc)

> With these Top 1 Table2.Id:s I finally want to Select all data
> From Table2 Where each of the Id:s are equal to either Table2.Id Or
> Table2.ParentId
SELECT Id, ParentId, Table1Id, Type, Title
FROM Table2
WHERE Id IN
(
SELECT t2.Id as Table2ID
FROM Table1 t1
INNER JOIN Table2 t2 ON t2.Table1Id=t1.Id AND t2.ParentId=0
AND t2.Id=(SELECT TOP 1 t3.Id
FROM Table2 t3
WHERE t3.Table1Id=t1.Id AND
t3.ParentId=0
ORDER BY t3.Type DESC,Id Desc)
)
OR ParentId IN
(
SELECT t2.Id as Table2ID
FROM Table1 t1
INNER JOIN Table2 t2 ON t2.Table1Id=t1.Id AND t2.ParentId=0
AND t2.Id=(SELECT TOP 1 t3.Id
FROM Table2 t3
WHERE t3.Table1Id=t1.Id AND
t3.ParentId=0
ORDER BY t3.Type DESC,Id Desc)
)|||That works great. You made my day. A big thank you for your time and knowled
ge.
/ Senna
"markc600@.hotmail.com" wrote:

>
> SELECT t2.Id as Table2ID
> FROM Table1 t1
> INNER JOIN Table2 t2 ON t2.Table1Id=t1.Id AND t2.ParentId=0
> AND t2.Id=(SELECT TOP 1 t3.Id
> FROM Table2 t3
> WHERE t3.Table1Id=t1.Id AND
> t3.ParentId=0
> ORDER BY t3.Type DESC,Id Desc)
>
> SELECT Id, ParentId, Table1Id, Type, Title
> FROM Table2
> WHERE Id IN
> (
> SELECT t2.Id as Table2ID
> FROM Table1 t1
> INNER JOIN Table2 t2 ON t2.Table1Id=t1.Id AND t2.ParentId=0
> AND t2.Id=(SELECT TOP 1 t3.Id
> FROM Table2 t3
> WHERE t3.Table1Id=t1.Id AND
> t3.ParentId=0
> ORDER BY t3.Type DESC,Id Desc)
> )
> OR ParentId IN
> (
> SELECT t2.Id as Table2ID
> FROM Table1 t1
> INNER JOIN Table2 t2 ON t2.Table1Id=t1.Id AND t2.ParentId=0
> AND t2.Id=(SELECT TOP 1 t3.Id
> FROM Table2 t3
> WHERE t3.Table1Id=t1.Id AND
> t3.ParentId=0
> ORDER BY t3.Type DESC,Id Desc)
> )
>