Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2002
    Posts
    5

    Unanswered: I have a Subquery (nested) in Access can't figure it out in ASP/vbscript

    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

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: I have a Subquery (nested) in Access can't figure it out in ASP/vbscript

    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.

  3. #3
    Join Date
    Dec 2002
    Posts
    5

    Re: I have a Subquery (nested) in Access can't figure it out in ASP/vbscript

    locations1 and locations2 *are* the subqueries

  4. #4
    Join Date
    Dec 2002
    Posts
    5

    Re: I have a Subquery (nested) in Access can't figure it out in ASP/vbscript

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

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: I have a Subquery (nested) in Access can't figure it out in ASP/vbscript

    Originally posted by newknew
    locations1 and locations2 *are* the subqueries
    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).

  6. #6
    Join Date
    Dec 2002
    Posts
    5

    Re: I have a Subquery (nested) in Access can't figure it out in ASP/vbscript

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

    Thanks,
    Tim

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •