Question for mayhew - Joining Tables On A Query

How can I join two queries into one.

I have two different queries and need to link them on the 'Store' field.

Query 1:
SELECT Stores.Store, StoreStatus, StoreType, Computer
From Stores, StoreDetails
WHERE Stores.Store = StoreDetails.Store
AND EXISTS (SELECT Parts.Part_ID
From Parts, PartDetails, TempStorage
WHERE Parts.Part_ID = PartDetails.Part_ID
AND Parts.Part_ID = TempStorage.Part_ID)

Query 2:
SELECT Store, PartName, PartNumber, PartType, InStock
From Parts, PartDetails, TempStorage
WHERE Parts.Part_ID = PartDetails.Part_ID
AND Parts.Part_ID = TempStorage.Part_ID

Response by mayhew:

Your first query is using EXISTS incorrectly.  I assume you want a list of stores that have parts in stock.

You need to include a reference to your main query in your sub query.
Otherwise you'll just get back everything from your main query as if you had no sub query in your where clause.

As far as linking them goes, you can do it by defining your two queries as tables and joining them.

select * from
(SELECT Stores.Store, StoreStatus, StoreType, Computer
From Stores, StoreDetails
WHERE Stores.Store = StoreDetails.Store
AND EXISTS (SELECT *
From Parts, PartDetails, TempStorage
WHERE Parts.Part_ID = PartDetails.Part_ID
AND Parts.Part_ID = TempStorage.Part_ID
AND Parts.Store = Stores.Store)) a,
(SELECT Store, PartName, PartNumber, PartType, InStock
From Parts, PartDetails, TempStorage
WHERE Parts.Part_ID = PartDetails.Part_ID
AND Parts.Part_ID = TempStorage.Part_ID) b
where a.store = b.store










Start Free Trial