Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2009
    Posts
    67

    Unanswered: SQL Noob working on a convulted DB!

    Ok, I'm not a complete noob, but I have this massive query I'm wanting to execute. Is there a maximum number of subqueries in a single query?

    Bottom line is this: the component table is 12,000 records +. I need all the components that:
    - the engineer created/owns (component.EnteredByID)
    - the engineer's team created/owns (component.EnteredByID)
    - the engineer's organization created/owns (component.customer)
    - and only the selected components (component.componentID)

    All of the above are defined in the engineer's profile. Schema to follow.

    Code:
    SELECT 
        components.AgilePartNumber, 
        components.ComponentID, 
        components.Description, 
        components.EnteredByID, 
        components.DateOfEntry, 
        components.ChangeOrderType, 
        components.CompEngrAssigned
    FROM components
    WHERE 
    (
        (components.CompEngrChecked = False)
        AND 
        (components.IsMostRecent = True)
        AND
        (        
            (CompEngrAssigned = 225)
            OR
            (
            /*
            TEAM MEMBERS MATCHING THE profileGroup ID in profileMembers
            */
            CompEngrAssigned IN 
                (
                    SELECT LoginID
                    FROM Authority
                    WHERE
                        Location IN
                        (
                            SELECT profileGroup.GroupName
                            FROM profileMembers, profileGroup
                            WHERE 
                                (profileMembers.subordinateID = profileGroup.GroupID)
                                AND
                                (profileMembers.compEngID = 225);
                        )
                    ORDER BY LoginID;
                )
            )
            OR
            (
            /*
            TEAM MEMBERS FROM profileMembers
            */
            CompEngrAssigned IN
                (
                    SELECT
                        Authority.LoginID
                    FROM profileMembers 
                    LEFT JOIN 
                        Authority ON 
                        profileMembers.subordinateID = Authority.AuthorityID
                    WHERE 
                    (
                        (profileMembers.subordinateID > 100)
                        AND 
                        (profileMembers.compEngID = 225)
                    );
                )
            )
            OR
            (
            ComponentID IN
                (
                    /*
                    CATEGORIES THAT MATCH THE profileCategories
                    */
                    SELECT configuration.ComponentID
                    FROM profileClassification
                    LEFT JOIN 
                        configuration ON
                        profileClassification.classification = configuration.classification
                    WHERE
                       (profileClassification.compEngID = 225)
                    ORDER BY configuration.ComponentID;
                )
            )
        )
    )
    ORDER BY components.DateOfEntry;
    * means the PhD eletrical engineer that developed this didn't consider PKs/FKs & just put the full string in the column.
    Code:
    +--------------------+
    | component          |
    +--------------------+
    | IntID (pk)         |
    | ComponentID (fk)   | (configuration.ComponentID)
    | Description        |
    | EnteredByID        |
    | DateOfEntry        |
    | ChangeOrderType    |
    | CompEngrAssigned   |
    +--------------------+
    
    +--------------------+
    | configuration      |
    +--------------------+
    | ComponentID (pk)   |
    | Classification *   | (profileClassification.classification)
    +--------------------+
    
    +--------------------+
    | Authority          |
    +--------------------+
    | AuthorityID (pk)   | (*.CompEngID)
    | LoginID            |
    | location *         |
    +--------------------+
    
    +-----------------------+
    | profileMembers        |
    +-----------------------+
    | profileMembersID (pk) |
    | CompEngID (fk)        | (Authority.AuthorityID)
    | subordinateID (fk)    | (Authority.AuthorityID)
    +-----------------------+
    
    +-----------------------+
    | profileClassification |
    +-----------------------+
    | ClassificationID (pk) |
    | CompEngID (fk)        | (Authority.AuthorityID)
    | classification *      | (configuration.classification)
    +-----------------------+
    
    +---------------------+
    | profileOrganization |
    +---------------------+
    | OrgID (pk)          |
    | CompEngID (fk)      | (Authority.AuthorityID)
    | OrgFPS1 (bool)      |
    | OrgFPS2 (bool)      |
    +---------------------+
    
    +--------------------+
    | profileGroup       |
    +--------------------+
    | GroupID (pk)       |
    | GroupName  *       | (Authority.location)
    +--------------------+
    OH, one last thing, this is all done in Access!! Not my choice and I just need to get it done! PLEASE HELP ME GROK THE SOLUTION!
    Last edited by LAYGO; 10-20-09 at 18:20.

  2. #2
    Join Date
    Oct 2009
    Posts
    67
    SUCCESS! I've got it returning rows. Without really paying attention, this line was a datatype mismatch:

    CompEngrAssigned = 225

    Also, the Electrical Engineer engineer that originally was developing this was mixes/matches what is stored in the database field. It's not the full text of the Authority.loginID that matches the component.CompEngAssigned. A perfect example of why you need FKs!

    So, I had to fudge the the selects a little:

    SELECT cSTR(Mid(LoginID, (InStr(LoginID, "\")+1)))

    Goes from "domain\username" to "username" because that's how the components.CompEngAssigned is filled!

    Now I just need to sort out why the WHERE clause isn't returning all of the rows.

  3. #3
    Join Date
    Oct 2009
    Posts
    67
    I think I've figured a way to do what I want, but I need some help with combining all these queries into one to return 1 result set.

    Does this sound like a temporary table solution?

    Code:
                    SELECT Mid(LoginID, (InStr(LoginID, "\")+1))
                    FROM Authority
                    WHERE
                        Location IN
                        (
                            SELECT profileGroup.GroupName
                            FROM profileMembers, profileGroup
                            WHERE 
                                (profileMembers.subordinateID = profileGroup.GroupID)
                                AND
                                (profileMembers.compEngID = 225);
                        )
                    ORDER BY LoginID;
    Code:
                    SELECT Mid(LoginID, (InStr(LoginID, "\")+1))
                    FROM profileMembers 
                    LEFT JOIN 
                        Authority ON 
                        profileMembers.subordinateID = Authority.AuthorityID
                    WHERE 
                    (
                        (profileMembers.subordinateID > 99)
                        AND 
                        (profileMembers.compEngID = 225)
                    );

  4. #4
    Join Date
    Oct 2009
    Posts
    67
    This works, but it's not returning all the rows expected. I suspect it has to do with the part of the WHERE clause dealing with the "EnteredByID IN ..." parts. Can I provide a second IN statement on the same column? Those subqueries work in and of themselves and return what I expect.

    Code:
    SELECT 
        components.AgilePartNumber, 
        components.ComponentID, 
        components.Description, 
        components.EnteredByID, 
        components.DateOfEntry, 
        components.ChangeOrderType, 
        components.CompEngrAssigned
    FROM components
    WHERE 
    (
        (components.CompEngrChecked = False)
        AND 
        (components.IsMostRecent = True)
        AND
        (
        ComponentID IN
            (
                SELECT configuration.ComponentID
                FROM profileClassification
                LEFT JOIN 
                    configuration ON
                    profileClassification.classification = configuration.classification
                WHERE
                   (profileClassification.compEngID = 225)
                ORDER BY configuration.ComponentID;
            )
        )
        AND
        (
    
    /****************** IS THIS SECTION BELOW VALID? ******************/
            (
            EnteredByID IN 
                (
                    SELECT Mid(LoginID, (InStr(LoginID, "\")+1))
                    FROM Authority
                    WHERE
                        Location IN
                        (
                            SELECT profileGroup.GroupName
                            FROM profileMembers, profileGroup
                            WHERE
                            (
                                (profileMembers.subordinateID = profileGroup.GroupID)
                                AND
                                (profileMembers.compEngID = 225)
                            );
                        )
                    ORDER BY LoginID;
                )
            )
            OR
            (
            EnteredByID IN
                (
                    SELECT Mid(LoginID, (InStr(LoginID, "\")+1))
                    FROM profileMembers 
                    LEFT JOIN 
                        Authority ON 
                        profileMembers.subordinateID = Authority.AuthorityID
                    WHERE 
                    (
                        (profileMembers.subordinateID > 99)
                        AND 
                        (profileMembers.compEngID = 225)
                    );
                )
            )
            OR
            (EnteredByID = "sdgajung")
    
    /****************** IS THIS SECTION ABOVE VALID? ******************/
    
        )
    )
    ORDER BY components.DateOfEntry;

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    The SQL is valid, though we cannot tell you if
    Code:
     EnteredByID IN
                (
                    SELECT Mid(LoginID, (InStr(LoginID, "\")+1))
    is a valid comparison as we do not know your data. I can't see the need for the order by that you are using. Also, to help with performance, you may want to get all of these ids into their own nested table, instead of all of the OR conditions and then use SQL to say something like(may not be completely correct, just quick cut n paste job here):
    EnteredByID IN
    ( SELECT Mid(LoginID, (InStr(LoginID, "\")+1))
    FROM Authority
    WHERE
    Location IN
    (
    SELECT profileGroup.GroupName
    FROM profileMembers, profileGroup
    WHERE
    profileMembers.subordinateID = profileGroup.GroupID
    AND
    profileMembers.compEngID = 225
    )
    UNION ALL

    SELECT Mid(LoginID, (InStr(LoginID, "\")+1))
    FROM profileMembers
    LEFT JOIN
    Authority ON
    profileMembers.subordinateID = Authority.AuthorityID
    WHERE
    profileMembers.subordinateID > 99
    AND
    profileMembers.compEngID = 225
    UNION ALL
    SELECT DISTINCT(EnteredByID)
    FROM components
    WHERE EnteredByID = "sdgajung")
    Dave Nance

  6. #6
    Join Date
    Oct 2009
    Posts
    67
    Quote Originally Posted by dav1mo
    The SQL is valid, though we cannot tell you if
    Code:
     EnteredByID IN
                (
                    SELECT Mid(LoginID, (InStr(LoginID, "\")+1))
    is a valid comparison as we do not know your data. I can't see the need for the order by that you are using.

    SELECT cSTR(Mid(LoginID, (InStr(LoginID, "\")+1)))

    Goes from "domain\username" to "username" because that's how the components.CompEngAssigned is filled!
    I had to do that because the engineer didn't understand how to use PKs/FKs. As of this morning, we're going to do some data modifications to the Components table to add 2 more columns, one of them being the AuthorityID FK, and that will be unnecessary in the very near future.

    Quote Originally Posted by dav1mo
    Also, to help with performance, you may want to get all of these ids into their own nested table, instead of all of the OR conditions and then use SQL to say something like(may not be completely correct, just quick cut n paste job here):


    Dave Nance
    ALAS! I think the UNION statement is EXACTLY what I was looking for! Damn, I don't write enough SQL to remember them all, but ya, the UNION statement will join both of those queries results & will work perfectly . . . I think. I have some other tasks to tackle first & I'll be back this afternoon with the results.

    Thanks Dave!

  7. #7
    Join Date
    Oct 2009
    Posts
    67
    Damn, running behind, dunno if I'll get to work on that query until later tonight.

  8. #8
    Join Date
    Oct 2009
    Posts
    67
    Here I am again. I got a lot done as far as convincing the engineer to start using PKs/FKs, but it's still a long way to go. There's a lot of scripts still referencing the text name in the column. Those will be updated in the future.

    Ok, now my current issue. Thanks to Dave for the UNION idea, it works great . . . but not in a subquery! Unless there is another way to do this w/o a subquery, great, but right now I don't see it.

    This works great by itself:
    Code:
    SELECT AuthorityID
    FROM Authority
    WHERE
        Location IN
        (
            SELECT profileSite.SiteName
            FROM profileMembers, profileSite
            WHERE 
                (profileMembers.subordinateID = profileSite.SiteID)
                AND
                (profileMembers.compEngID = 225);
        )
    ORDER BY LoginID;
    
    UNION ALL
    
    SELECT
        Authority.AuthorityID
    FROM profileMembers 
    LEFT JOIN 
        Authority ON 
        profileMembers.subordinateID = Authority.AuthorityID
    WHERE 
    (
        (profileMembers.subordinateID > 100)
        AND 
        (profileMembers.compEngID = 225)
    );
    Access is saying "NUH UH" to the UNION in a SUBQUERY here:
    Code:
    SELECT components.AgilePartNumber
         , components.ComponentID
         , components.Description
         , components.EnteredID
         , components.DateOfEntry
         , components.ChangeOrderType
         , components.CompEngrAssigned
         , Authority.FirstName
         , Authority.LastName
    FROM components
    INNER JOIN Authority 
        ON Authority.AuthorityID = components.EnteredID
    WHERE 
    (
        (components.CompEngrChecked = False)
        AND 
        (components.IsMostRecent = True)
        AND
        (
            ComponentID IN
            (
                SELECT configuration.ComponentID
                FROM profileClassification
                LEFT JOIN 
                    configuration ON
                    profileClassification.classification = configuration.classification
                WHERE
                   (profileClassification.compEngID = 225)
                ORDER BY configuration.ComponentID;
            )
        )
        AND
        (        
            (
                EnteredID IN 
                (
                    SELECT AuthorityID
                    FROM Authority
                    WHERE
                        Location IN
                        (
                            SELECT profileSite.SiteName
                            FROM profileMembers, profileSite
                            WHERE 
                                (profileMembers.subordinateID = profileSite.SiteID)
                                AND
                                (profileMembers.compEngID = 225);
                        )
                    ORDER BY LoginID;
                    
                    UNION ALL
                    
                    SELECT
                        Authority.AuthorityID
                    FROM profileMembers 
                    LEFT JOIN 
                        Authority ON 
                        profileMembers.subordinateID = Authority.AuthorityID
                    WHERE 
                    (
                        (profileMembers.subordinateID > 100)
                        AND 
                        (profileMembers.compEngID = 225)
                    );
                )
            )
            OR
            (EnteredID = 255)
        )
    )
    ORDER BY components.DateOfEntry;
    Any feedback on how to pull that off?

    Revised schema, but not all that different:
    Code:
    +---------------------------+
    | component                 |
    +---------------------------+
    | InternalNumber (pk)       |
    | ComponentID (fk)          | configuration.ComponentID
    | Description               |
    | EnteredByID               |
    | EnteredID (fk)            | Authority.AuthorityID 
    | DateOfEntry               |
    | ChangeOrderType           |
    | CompEngrAssigned          |
    | CompEngrAssignedID (fk)   |
    +---------------------------+
    
    +--------------------+
    | configuration      |
    +--------------------+
    | ComponentID (pk)   |
    | Classification *   |
    +--------------------+
    
    +--------------------+
    | Authority          |
    +--------------------+
    | AuthorityID (pk)   |
    | LoginID            |
    | location *         |
    +--------------------+
    
    +-----------------------+
    | profileMembers        |
    +-----------------------+
    | profileMembersID (pk) |
    | CompEngID (fk)        | Authority.AuthorityID
    | subordinateID (fk)    | Authority.AuthorityID
    +-----------------------+
    
    +-----------------------+
    | profileClassification |
    +-----------------------+
    | ClassificationID (pk) |
    | CompEngID (fk)        | Authority.AuthorityID
    | classification *      | text match of configuration.classification
    +-----------------------+
    
    +---------------------+
    | profileOrganization |
    +---------------------+
    | OrgID (pk)          |
    | CompEngID (fk)      | Authority.AuthorityID
    | OrgFPS1 (bool)      |
    | OrgFPS2 (bool)      |
    +---------------------+
    
    +--------------------+
    | profileGroup       |
    +--------------------+
    | GroupID (pk)       |
    | GroupName  *       | text match of Authority.location
    +--------------------+
    Last edited by LAYGO; 10-22-09 at 14:22.

  9. #9
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I don't use access and purposely stay away from it. How about a nested table expression that you join to? Something like:
    Code:
    Select cols from
        TABLE(SELECT MYID
                     FROM TABLEA
                 UNION ALL
                 SELECT MYID 
                     FROM TABLEB) AS MYIDTABLE
    INNER JOIN TABLE_I_WANT_DATA_FROM
    WHERE MYIDTABLE.MYID = THIS_ID
    ???

    Dave Nance

  10. #10
    Join Date
    Oct 2009
    Posts
    67
    Quote Originally Posted by dav1mo
    I don't use access and purposely stay away from it. How about a nested table expression that you join to? Something like:
    Code:
    Select cols from
        TABLE(SELECT MYID
                     FROM TABLEA
                 UNION ALL
                 SELECT MYID 
                     FROM TABLEB) AS MYIDTABLE
    INNER JOIN TABLE_I_WANT_DATA_FROM
    WHERE MYIDTABLE.MYID = THIS_ID
    ???

    Dave Nance
    Thanks Dave. I wouldn't use Access either. It's really crazy what an Electrical Engineer PhD can come up with with some free time and a need w/o outsourcing it. He no longer has the time to maintain it & contracted me to try and figure this all out. I've got a grasp thus far, but I feel like I'm running into one road block after another.

    To top it off, he has access to SQL Server, but elects to NOT use it!

    He's a great guy but gives no thought to web usability and virtually ignores my suggestions. I think I should tell him how to build power supplies!

    Again, many thanks Dave.

  11. #11
    Join Date
    Oct 2009
    Posts
    67
    Access support of this is lacking, so I will just be doing it programatically providing the " IN (listHere) " statement. I was hoping to do it in a single query for ease of management.

  12. #12
    Join Date
    Oct 2009
    Posts
    67
    NEW PROBLEM! I'd love to see this solution.

    In part, I can't figure out how to return 1 row with 2 different users' names.

    I need the name of the engineer assigned to the component and the name of engineer who created the component. I have both IDs in the same row, how do I make both ids referring to the same table give 2 different responses?

    I'm googling away . . .

    This is kinda like what I'm talking about, but obviously not Oracle. I'm not sure how to 'translate' this because many of those use Oracle specific functions.
    Oracle SQL: displaying multiple columns per row

    Figured it out. Tada!
    Code:
    SELECT 
        components.InternalNumber
      , Authority.AuthorityID
      , Authority.FirstName
      , Authority.LastName
      , Authority_1.AuthorityID
      , Authority_1.FirstName
      , Authority_1.LastName
    FROM Authority AS Authority_1 
    INNER JOIN 
    (
        Authority 
        INNER JOIN 
        components 
        ON Authority.AuthorityID = components.EnteredID
    ) 
    ON Authority_1.AuthorityID = components.CompEngrAssignedID;
    Last edited by LAYGO; 10-23-09 at 11:53.

Posting Permissions

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