Results 1 to 11 of 11
  1. #1
    ccla is offline Novice
    Windows 10 Office 365
    Join Date
    Feb 2021
    Posts
    8

    Post UNION CLAUSE in sub queries

    Dear all,



    I have a quick question. Can a UNION clause be used in a sub query? I ask because I have the following query that works in limiting the values in a combo box:

    SELECT Lens.LensID, Company.CompanyName & " " & LensMount.LensMountName & " " & Lens.[Model] AS LensByCompanyAndModel, Lens.Model,
    Lens.CompanyID, Lens.Modifier, Lens.SerialNumber, Lens.LensMountID, Lens.ApertureIncrements,
    Lens.ApertureFast, Lens.ApertureSlow, Lens.FocalLengthShort, Lens.ThreadSizemm, Lens.Zoom, Lens.VariableAperture,
    Lens.FocalLengthLong, Lens.ApertureFastLong, Company.CompanyName, LensMount.LensMountName

    FROM LensMount INNER JOIN (Company INNER JOIN Lens ON Company.ID = Lens.[CompanyID]) ON LensMount.[ID] = Lens.LensMountID

    WHERE Lens.LensMountID IN (
    SELECT CameraToDirectMount.MountID
    FROM CameraToDirectMount
    WHERE CameraToDirectMount.CameraID = 2
    )

    ORDER BY Company.CompanyName & " " & LensMount.LensMountName & " " & Lens.[Model];

    However the following query will not work (the query with the second SELECT statement only also works on its own):

    SELECT Lens.LensID, Company.CompanyName & " " & LensMount.LensMountName & " " & Lens.[Model] AS LensByCompanyAndModel, Lens.Model,
    Lens.CompanyID, Lens.Modifier, Lens.SerialNumber, Lens.LensMountID, Lens.ApertureIncrements,
    Lens.ApertureFast, Lens.ApertureSlow, Lens.FocalLengthShort, Lens.ThreadSizemm, Lens.Zoom, Lens.VariableAperture,
    Lens.FocalLengthLong, Lens.ApertureFastLong, Company.CompanyName, LensMount.LensMountName

    FROM LensMount INNER JOIN (Company INNER JOIN Lens ON Company.ID = Lens.[CompanyID]) ON LensMount.[ID] = Lens.LensMountID

    WHERE Lens.LensMountID IN (
    SELECT CameraToDirectMount.MountID
    FROM CameraToDirectMount
    WHERE CameraToDirectMount.CameraID = 2

    UNION

    SELECT LensAccessory.LensMountToID
    FROM LensAccessory
    WHERE LensAccessory.ID = 8
    )

    ORDER BY Company.CompanyName & " " & LensMount.LensMountName & " " & Lens.[Model];

    If the second SELECT statement statement within the UNION statement is used on its own, that statement also works, so it seems that I cannot use UNION statement in sub queries. Is this correct or am I doing something else wrong?

    Also for some reason I cannot use the calculated field for the ORDER BY clause. Even with the single select statement if I substitute LensByCompanyAndModel for the formula in the ORDER BY clause, I get an error.

    Thank you for any help.

    claudio

  2. #2
    ccla is offline Novice
    Windows 10 Office 365
    Join Date
    Feb 2021
    Posts
    8
    Sorry,
    I guess leading spaces are removed from the post do the queries do not look pretty.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    go back and put code tags around all that sql and add indentation to solve the presentation issue. Cut, click # on post toolbar then paste. Suggest you not include your commentary/questions, so you'll need to do that for each code section and intersperse your comments between groups of code.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Indulge me. I just put the Poor Mans Sql formatter plug-in in my NotePad++ so i'll post for you.
    It's available in the plug-in manager of the new version of Notepad++.

    ccla, as you can see the code tags and formatting are very important for readability.

    Code:
    SELECT Lens.LensID
        ,Company.CompanyName & " " & LensMount.LensMountName & " " & Lens.[Model] AS LensByCompanyAndModel
        ,Lens.Model
        ,Lens.CompanyID
        ,Lens.Modifier
        ,Lens.SerialNumber
        ,Lens.LensMountID
        ,Lens.ApertureIncrements
        ,Lens.ApertureFast
        ,Lens.ApertureSlow
        ,Lens.FocalLengthShort
        ,Lens.ThreadSizemm
        ,Lens.Zoom
        ,Lens.VariableAperture
        ,Lens.FocalLengthLong
        ,Lens.ApertureFastLong
        ,Company.CompanyName
        ,LensMount.LensMountName
    FROM LensMount
    INNER JOIN (
        Company INNER JOIN Lens ON Company.ID = Lens.[CompanyID]
        ) ON LensMount.[ID] = Lens.LensMountID
    WHERE Lens.LensMountID IN (
            SELECT CameraToDirectMount.MountID
            FROM CameraToDirectMount
            WHERE CameraToDirectMount.CameraID = 2
            )
    ORDER BY Company.CompanyName & " " & LensMount.LensMountName & " " & Lens.[Model];
    Code:
    SELECT Lens.LensID
        ,Company.CompanyName & " " & LensMount.LensMountName & " " & Lens.[Model] AS LensByCompanyAndModel
        ,Lens.Model
        ,Lens.CompanyID
        ,Lens.Modifier
        ,Lens.SerialNumber
        ,Lens.LensMountID
        ,Lens.ApertureIncrements
        ,Lens.ApertureFast
        ,Lens.ApertureSlow
        ,Lens.FocalLengthShort
        ,Lens.ThreadSizemm
        ,Lens.Zoom
        ,Lens.VariableAperture
        ,Lens.FocalLengthLong
        ,Lens.ApertureFastLong
        ,Company.CompanyName
        ,LensMount.LensMountName
    FROM LensMount
    INNER JOIN (
        Company INNER JOIN Lens ON Company.ID = Lens.[CompanyID]
        ) ON LensMount.[ID] = Lens.LensMountID
    WHERE Lens.LensMountID IN (
            SELECT CameraToDirectMount.MountID
            FROM CameraToDirectMount
            WHERE CameraToDirectMount.CameraID = 2
            
            UNION
            
            SELECT LensAccessory.LensMountToID
            FROM LensAccessory
            WHERE LensAccessory.ID = 8
            )
    ORDER BY Company.CompanyName & " " & LensMount.LensMountName & " " & Lens.[Model];
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I don't know the answer to the original question per se but as written, there would be no comma separator between the two union parts that make up the IN operator so that should be an issue. Plus, if the union returns more than 1 record in either half, that's more IN parts that are not separated so IMO that means a code solution only - loop through the recordset created by the union and concatenate the returned data with comma separators.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    ccla is offline Novice
    Windows 10 Office 365
    Join Date
    Feb 2021
    Posts
    8
    Thank you so much moke123! I found the web site for the Poor SQL and will be using it in the future (I agree formatting is very important for readability and that is why I tried to format it using spaces only to have them all removed. From now on I will use the code tag as well).

    Micron, I am not sure if you refer to the statement itself (I assume not as there are no commas between the two union statement) or the set of values returned from the UNION statement. If this is the case, I am not sure I understand. As I understand it a select statement returns a set (maybe only one) of values. The IN clause checks if one value is part of that set.
    As a matter of fact in the database I have two direct mounts for Canon cameras: FL and FD. All Canon cameras with an FD mount can directly mount both FD and FL lenses, so this means that when I run the first statement, in the drop down list I get both FL and FD lenses (the CameraToDirectMount is a many-to-many helper table to keep track of all the Lens Mounts that can be DIRECTLY - i.e. with no accessory - be mounted on a particular camera).
    Now, we can also use a Lens Accessory to mount, let's say, a K mount lens (made by Pentax) onto an FD camera. That is why I need the second statement. If I have a lens adapter, then not only can I mount FD and FL lenses, but also the K lenses on an FD camera; that is where the UNION clause comes in, I want to select all direct mounts lenses plus the K lenses that I can use because of the lens adapter.
    The first statement works if the select statement returns a single value (i.e. if I only have FD lenses) and if it returns multiple values (i.e. I own both FD and FL lenses), so I do not think it is a comma issue. The problem is that when I use the UNION clause, the statement generates the following error: "This Operation is nor allowed in subqueries".
    I searched the net and apparently this is not allowed in Web Based Access application, but it seems it should work in desktop application which according to my test is not true. Thus my original questions.

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I'm not sure all that stuff about camera lenses is important for your issue and that's with being able to say I taught photography in the past. The only time I've ever used an IN operator was as a criteria expression in a query field; e.g. IN(1,2,3,4) and all values had to be separated. If you're saying UNION1 returns 2 values and UNION2 returns 2 values and the IN operator will either deal with them separately or actually comma delimit them, then I learned something. Perhaps we won't know until you get around the error. Your last post was the first time you mentioned that there was an error which is kind of critical info?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    ccla is offline Novice
    Windows 10 Office 365
    Join Date
    Feb 2021
    Posts
    8
    Yep, sorry about not giving the error. I could not figure out why the statement was not working, so I took a different approach. Instead of using the UNION in the where clause, I used the UNION to combine two separate SELECT statement as follows:

    Code:
    SELECT Lens.LensID
        ,Company.CompanyName & " " & LensMount.LensMountName & " " & Lens.[Model] AS LensByCompanyAndModel
        ,Lens.Model
        ,Lens.CompanyID
        ,Lens.Modifier
        ,Lens.SerialNumber
        ,Lens.LensMountID
        ,Lens.ApertureIncrements
        ,Lens.ApertureFast
        ,Lens.ApertureSlow
        ,Lens.FocalLengthShort
        ,Lens.ThreadSizemm
        ,Lens.Zoom
        ,Lens.VariableAperture
        ,Lens.FocalLengthLong
        ,Lens.ApertureFastLong
        ,Company.CompanyName
        ,LensMount.LensMountName
    FROM LensMount
    INNER JOIN (
        Company INNER JOIN Lens ON Company.ID = Lens.[CompanyID]
        ) ON LensMount.[ID] = Lens.LensMountID
    WHERE Lens.LensMountID IN (
            SELECT CameraToDirectMount.MountID
            FROM CameraToDirectMount
            WHERE CameraToDirectMount.CameraID = 2
            )
    
    UNION
    
    SELECT Lens.LensID
        ,Company.CompanyName & " " & LensMount.LensMountName & " " & Lens.[Model] AS LensByCompanyAndModel
        ,Lens.Model
        ,Lens.CompanyID
        ,Lens.Modifier
        ,Lens.SerialNumber
        ,Lens.LensMountID
        ,Lens.ApertureIncrements
        ,Lens.ApertureFast
        ,Lens.ApertureSlow
        ,Lens.FocalLengthShort
        ,Lens.ThreadSizemm
        ,Lens.Zoom
        ,Lens.VariableAperture
        ,Le ns.FocalLengthLong
        ,Lens.ApertureFastLong
        ,Company.CompanyName
        ,LensMount.LensMountName
    FROM LensMount
    INNER JOIN (
        Company INNER JOIN Lens ON Company.ID = Lens.[CompanyID]
        ) ON LensMount.[ID] = Lens.LensMountID
    WHERE Lens.LensMountID IN (
            SELECT LensAccessory.LensMountToID
            FROM LensAccessory
            WHERE LensAccessory.ID = 8
            )
    ORDER BY Company.CompanyName & " " & LensMount.LensMountName & " " & Lens.[Model];
    However I now get a different error: "The ORDER BY expression (Company.CompanyName & " " & LensMount.LensMountName & " " & Lens.[Model]) includes fields that are not selected by the query. Only those fields requested in the first query can be included in an ORDER BY expression."

    WHAT? The fields are right there in both SELECT statements. What am I doing wrong?

  9. #9
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    This exercise may be easier to trouble shoot if you post a database with the relevant tables and queries.

    And an after thought, have you tried "Order By LensByCompanyAndModel" since you are aliasing them in both queries?
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    try

    ORDER
    BY CompanyName, LensMountName, [Model];

    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    ccla is offline Novice
    Windows 10 Office 365
    Join Date
    Feb 2021
    Posts
    8
    Quote Originally Posted by moke123 View Post
    This exercise may be easier to trouble shoot if you post a database with the relevant tables and queries.

    And an after thought, have you tried "Order By LensByCompanyAndModel" since you are aliasing them in both queries?
    Thank you again moke123. I first tried Micron's suggestion but that came up with the same error. I had originally tried to use calculated field (LensByCompanyAndModel) in my first query, but I received an error. Fortunately I tried your suggestion anyway and low and behold and it works beautifully. Now when I use the combo box there they are, my FD, FL, and K mount lenses.
    I have been at this, on an off, for more than a week, but finally resolved it.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Union Queries into SQL
    By ortizimo in forum Queries
    Replies: 2
    Last Post: 10-09-2017, 09:13 AM
  2. queries based on union queries
    By vicsaccess in forum Queries
    Replies: 3
    Last Post: 10-10-2015, 07:53 PM
  3. Union query- 9 Queries
    By JessieBee in forum Queries
    Replies: 3
    Last Post: 07-14-2012, 05:46 AM
  4. One selection clause for many queries
    By dougie in forum Access
    Replies: 7
    Last Post: 01-05-2012, 01:06 AM
  5. Union Queries
    By tarhim47 in forum Queries
    Replies: 6
    Last Post: 05-26-2011, 12:20 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums