Running a query off the results of another

[jondel81]

TheQuery:
SELECT DISTINCT ItemFile.GroupID, ITEM.NBR
FROM ITEM, ItemFile
WHERE (((ITEM.NBR) Between [ItemFile].[Range_Low] And [ItemFile].[Range_High]));
UNION SELECT Group_Items.GroupID, Group_Items.NBR
FROM Group_Items;

Second Query:
SELECT TheQuery.GroupID, GroupItems.NBR
FROM TheQuery RIGHT JOIN TheQuery AS GroupItems ON TheQuery.GroupID = GroupItems.GroupID
WHERE (((TheQuery.NBR)="4874095M"));

You see how the second query calls on “TheQuery”. How do I make these two queries one query?


------

http://www.carealotpets.com

 

[SQLSister]

Check out derived tables in Books Online. The critical thing with a derived table is you must give it an alias. Exaxmple:

select a.itemno, field1 from table1 join (select idfield, itemno from table2 where date= '10/1/2004') a
on table1.idfield = a.idfield

Questions about posting. See faq183-874

 

[vongrunt]

Cookbook guide: insert (first query) between "FROM" and "TheQuery".

 

[jondel81]

You notice it does call "TheQuery" twice. Do I need to put the "TheQuery" in twice? If so were?

Something like this? I could not get it to work.

SELECT TheQuery.GroupID, GroupItems.NBR
FROM (here) TheQuery RIGHT JOIN (here) AS GroupItems ON TheQuery.GroupID = GroupItems.GroupID
WHERE (((TheQuery.NBR)="4874095M"));

------

http://www.carealotpets.com