Hi I have 2 different databases. On the first database i have run the following: select c.main_number, c.sub_number, d.document_id, dd.document_description from car c, document d, document_description dd where c.main_number=d.main_number and dd.document_id=d.document_id and dd.document_description like '%test%' This has given me results like so: main_number sub_number document_id document_description 23 456 98 test doc 65 8596 91 Test Document 42 2 85 This is a test 63 7 99 Test Document On the second i have run the following: select f1.id, f1.name, f1.id_type, f1.id_parent, f2.name, c.col4, col5 from folders f1, folders f2, cat c where f1.id_parent=c.id and f1.id_type = '1' and f1.id not in (SELECT id FROM docs) and f1.id_parent=f2.id this has given me results like this: f1.id f1.name f1.id_type f1.id_parent f2.name c.col4 c.col5 th1 car 1 th2 machine 23 456 th3 zebra 1 th4 cat 11 11 th5 couch 1 th6 box 42 2 th7 floor 1 th8 table 10 10 My question is how do i incorporate the second SQL into the first SQL? Would it be possible to have the second SQL as a ‘Where’ on the first SQL? Using the example above i would want only ‘main number’ 23 & 42 in the result. I am looking to do this in one SQL without creating any temp tables if possible. Hope i have explained this ok!! Many thanks

Hi Paul Are the two databases on the same server? If not, would you be allowed to create a linked server between the two? Craig Bryden - Please mark correct answers

Hi Paul, Are they different databases on the same box or different database servers? If they are two different databases on the same box, you will be able to merge both the scripts into a single one by using Three Part Names (database.schema.table). If they are databases on different servers, then you might want to look into setting up a Linked Server as Craig suggests. Assuming the former, the tables in your second query would be referenced as - [Database2].[dbo].[folders] AS f1 - [Database2].[dbo].[cat] AS c You could then join to the resultset of your first query either in the WHERE clause (if you went by ANSI - 89 syntax) or could also use an INNER JOIN (if you went by ANSI - 92) on the c.[Col4] and the Main_Number fields. You would not need temp tables. HTH. Cheers, IceQB

Hi They are on different servers but i am getting them linked. The person who is doing it says he is having problems with permissions??? iceqb - how would i joing the resultset to my first query using the WHERE cluase? Many thanks

Hey Paul, You could do this: WITH CTE AS ( SELECT f1.id, f1.name, f1.id_type, f1.id_parent, f2.name, c.col4, col5 FROM Server.Database.Schema.folders f1, Server.Database.Schema.folders f2, Server.Database.Schema.cat c WHERE f1.id_parent=c.id AND f1.id_type = '1' AND f1.id not in (SELECT id FROM Server.Database.Schema.docs) AND f1.id_parent=f2.id ) SELECT c.main_number, c.sub_number, d.document_id, dd.document_description, CTE.* FROM car c, document d, document_description dd, CTE WHERE c.main_number=d.main_number AND c.main_number = CTE.Col4 AND dd.document_id=d.document_id AND dd.document_description LIKE '%test%'   Here Server, Database and Schema refer to the linked server name, the database name on that linked server and the associated schema. Hope this helps! Cheers, IceQB

Hi IceQB Many thanks for your help. Once i have the problem sorted with the linked servers i will give it a try. Will let you know how it goes!!! Mant thanks