I have 2 sql statements from one Table. The second uses the first in an inner join. Can I make 1 sql instead?

Question

SQL 1
SELECT lists.tp_ListId, lists.float1, lists.tp_ID
FROM lists
WHERE (((lists.tp_ListId)={guid {51ADAD28-41BD-4D04-9B28-F0FA2A26AE35}}));


SQL 2
SELECT lists.tp_ListId, DatePart('yyyy',[lists].[dateTime1]) AS [Year], DatePart('ww',[lists].[dateTime1]) AS WeekofYear, [dev_lstAssembly].float1
FROM lists INNER JOIN dev_lstAssembly ON lists.int1=[dev_lstAssembly].tp_ID
WHERE (((lists.tp_ListId)={guid {E10FA531-1F21-455F-8D9C-AA85AE1838F1}}));

Is there a way to combine 1 into 2?

SQL 1 is called dev_lstAssembly

Basically I have a Microsoft Sharepoint database and all my data is stored in one table. Thats why the first query extracts the the assembly table (sharepoint list) and the second extracts finished goods list.

Thanks for your help.

Hey,
So i have uploaded an access database file
http://www.gatehouseministries.org/akshay/new_sharepointgrapher.mdb

I have kept the same table structure as the sql database. The list I am trying to get is the dev_lstChannel view.

I would ideally like to get that same view in one SQL statement!

Answer 1

EDIT 2:
SELECT lists.tp_ListId, DatePart('yyyy',[lists].[dateTime1]) AS [Year], DatePart('ww',[lists].[dateTime1]) AS WeekofYear,
[XXX].float1
FROM lists INNER JOIN [SELECT
lists.tp_ListId,
lists.float1,
lists.tp_ID
FROM lists
WHERE (((lists.tp_ListId)={guid {51ADAD28-41BD-4D04-9B28-F0FA2A26AE35}}))
]. AS XXX ON lists.int1=[XXX].tp_ID
WHERE (((lists.tp_ListId)={guid {E10FA531-1F21-455F-8D9C-AA85AE1838F1}}));

sql1 as XXX now.

EDIT 1:
hmm... i never see microsoft sharepoint database.
anyway, give me your table structure with some sample data, and tell me what you want from them. maybe i can help you to build the query.


OLD ANSWER :
are
lists.float1 = DatePart('yyyy',[lists].[dateT... AS [Year] in SQL2 ?????
and
lists.tp_ID = DatePart('ww',[lists].[dateTim... AS WeekofYear, [dev_lstAssembly].float1 ?????

if that so....... use 'UNION ALL'

SELECT lists.tp_ListId, lists.float1, lists.tp_ID
FROM lists
WHERE (((lists.tp_ListId)={guid {51ADAD28-41BD-4D04-9B28-F0FA2...
UNION ALL
SELECT lists.tp_ListId, DatePart('yyyy',[lists].[dateT... AS [Year], DatePart('ww',[lists].[dateTim... AS WeekofYear, [dev_lstAssembly].float1
FROM lists INNER JOIN dev_lstAssembly ON lists.int1=[dev_lstAssembly].t...
WHERE (((lists.tp_ListId)={guid {E10FA531-1F21-455F-8D9C-AA85A...

Answer 2

a million) be sure you have indexes on the connect columns. 2) You lose some velocity with the diverse key-word, because of the fact it has to examine throughout the 50,000 information for duplicates. 3) If neither of those help, you ought to attempt a distinctive table company.

Answer 3

Code efficiency is always the best, however in this case it would only hurt you since the results and sorting them is what important.
The joint section will provide you with additional results to what you are already receiving in SQL1.
Unfortunately I would not recommend the combination unless the results are such that you can easily separate them in your programming language, for example: results such as names and dates are easy to separate. Numbers and dates are harder...