Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2012
    Posts
    5

    Unanswered: Combing SQL result into another SQL

    Hi

    Within the xml of an eForm there is a field called 'Departments' which includes a long list of possible entries. When completing the eform itself there are 7 dropdown fields that the Department list is included against, therefore up to 7 columns in a single row of the table could contain a Department name.

    I assume that I would firstly need to create an sql that will list all of the possible Departments -

    SELECT DISTINCT DEPARTMENTS
    FROM EFORM1_TABLE

    But I then need to write an sql to use the details from the above to look in the 7 columns where the data may or may not be and return those rows where 1 or more columns match. This is where I am stuck.

    The list of departments is included in a field called 'DEPARTMENTS', but the 7 columns that may or may not contain 1 or more of these entries are called DEPARTMENT1, DEPARTMENT2, ETC. How do I use the first to select data from the other 7?

    Thanks.

    Francis

  2. #2
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Let me re-phrase your issue . . .

    You have a list of departments.

    [in your pseudo-SQL, why are you doing a DISTINCT against this list of departments? If you have a list of departments, isn't that list already a unique list of departments?]

    And you have seven fields, which may contain departments from this list, or may be empty.

    [can they contain departments that are not in this main list of departments? If so, why?]

    If all of the departments in the sevel comboboxes are members of the main list of departments, why do you need to join anything? Are there other fields of data in that main list of departments that you need?
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  3. #3
    Join Date
    Jun 2012
    Posts
    5
    Hi

    My sql skills are rather limited, so please forgive any misunderstanding with my explanation.

    The SQL to list the departments searches the data that has been selected during testing rather than the actual department list held within the xml. Therefore, without the inclusion of DISTINCT I end up with 46 rows with numerous repeating results. With DISTINCT I end up with 21, which is the amount I expected.

    The seven fields can only be either blank or contain one of the 21 departments in the list, no other entries can be made.

    I'm using Microsoft Visual Studio to complile a report based on the results from Microsoft SQL Server. I've set up a Parameter to allow the person running the report to select all or specific departments. I have compiled this parameter list using the sql to list the departments from the field DEPARTMENT, but it doesn't want to play ball when examining the fields in the report which are called DEPARTMENT1, DEPARTMENT2, etc. So I'm not sure if I need to ignore the sql listing DEPARTMENT and instead create a new SQL which combines the available entries in DEPARTMENT1, DEPARTMENT2, etc. I thinking about going back to the xml and creating a hidden field that combines all departments selected into a single area which will then allow the search function to work?

    Thanks.

    Francis

Posting Permissions

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