In Access, I have a series of queries that are required by the main query because of multiple autolookups that are based on the same key in the lookup table. Let me explain:

I have a table of Shipments. The Shipments table has fields for Customer, Shipper, Consignee, and BillTo. These fields are all related to the same ID field in a table called Locations and likewise store the ID of the associated Location. Therefore, a single record in Shipments could have the same ID from Locations for Customer, Shipper, Consignee and/or BillTo.

I want to use a lookup query to display the full name of the Location. In Access, I have created a set of subqueries to get the desired result. Subqueries are required because the Customer, Shipper, Consignee, and BillTo fields are ALL related to the same ID in Locations. Otherwise, Access is confused by ambiguous outer joins. In Access, I have achieved the desired result with no problems:

SELECT [shipments].[CustomerID], [locations].[Name], [shipments].[shipperid], [locations1].[Name], [shipments].[consigneeid], [locations2].[Name] FROM ((shipmentsLEFT JOIN locations ON [shipments].CustomerID]=[locations].[ID]) LEFT JOIN locations1 ON shipments].[shipperid]=[locations1].[ID]) LEFT JOIN locations2 ON shipments].[consigneeid]=[locations2].[ID];

(locations1 and locations2 are the names of the subqueries, or pre-queries... they are identical and look like this:

SELECT locations.* FROM locations;

)

Now, how do I port this to SQL so that I can use it in my VBScript/ASP code on my website? I would like to get the desired recordset with one SQL as opposed to running a quick "lookup query" everytime I need to lookup the name for an associated ID -- this would occur about 8 times on a page for the same record.

Thanks,
Tim

I don't understand why you should need pre-queries anyway. Why not just do this:

SELECT [shipments].[CustomerID], [locations].[Name], [shipments].[shipperid], [locations1].[Name], [shipments].[consigneeid], [locations2].[Name] FROM ((shipmentsLEFT JOIN locations ON [shipments].CustomerID]=[locations].[ID]) LEFT JOIN locations locations1 ON shipments].[shipperid]=[locations1].[ID]) LEFT JOIN locations locations2 ON shipments].[consigneeid]=[locations2].[ID];

i.e. use aliases.

locations1 and locations2 *are* the subqueries

locations1 and locations2 *are* the subqueries and this solution still uses them

Yes, in your query they were. In my query they were just aliases for the table locations. I was asking why you don't just use aliases. Using a simple example:

SELECT e1.name empname, e2.name mngrname
FROM emp e1, emp e2
WHERE e1.mngr_no = e2.empno;

i.e. you can use the same table more than once in a query without ambiguity, using aliases (e1 and e2 are aliases for emp).

Excellent! I tried it out and it works fine. It's nice to learn something in the process...

Thanks,
Tim