none
UNION SELECT SQL Query not returning second part of the statement RRS feed

  • Question

  • Hello, I am trying to conduct a multi-table SQL Query to scrape up top marks on a school project. Here is my code so far:

            'Split dataToBeQueried into patient_id and appointment_date
            Dim splitterArray() As String 'create a string that will be treated as an array of characters
            splitterArray = dataToBeQueried.Split(" ") 'split the array on every space
            Dim patientIdSearch As Integer = Int(splitterArray(0))
            Dim otherVar As Integer = Int(splitterArray(1))
    
    
            Dim con As New OleDbConnection
            con.ConnectionString = ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & fileDirectory & fileName)
            Dim da As New OleDbDataAdapter
            Dim ds As New DataSet
            Dim dt As New DataTable
            con.Open()
            ds.Tables.Add(dt) 'all this code establishes a connection to the database that is being queried
            da = New OleDbDataAdapter("SELECT Surname, Last_Appointment_Date FROM Patient WHERE Patient_ID =" & patientIdSearch &
               " UNION SELECT Appointment_Time_And_Time, Treatment_Cost FROM Appointments WHERE Appointment_ID =" & otherVar, con) 'feed in SQL query
            Try 'if it works populate Data Grid View
                da.Fill(dt)
                DataGridView4.DataSource = dt.DefaultView
            Catch ex As Exception 'if exception is thrown
                MsgBox(ex.Message)
            End Try
            con.Close() 'close the connection

    The actual code doesn't throw any exceptions or anything. I am trying to create a Query so that I can type in a patientID and an appointmentID so that I can return a joint table that will show me the patient's surname and their last appointment from the table Patient and their next appointment and its cost from the table Appointments. I know that data types can be important in SQL so here are the data types for each column:

    Surname: String

    Last_Appointment_Date: Date/Time Stamp

    Appointment_Time_And_Time: Date/Time Stamp

    Treatment_Cost: Currency

    However, when I run the code, with valid records in each table, it only return the Surname and the Lat_Appointment_Date columns. It doesn't display the other two. What am I doing wrong? Thanks :)

    Sunday, February 2, 2020 12:02 AM

All replies

  • Hello,

    Before making suggestions on what the problem may be, please consider using parameters for the WHERE conditions rather than use string concatenation.

    Now for the query, have no idea if the query was handwritten or done inside of MS-Access so my suggestion is to write the query using the query wizard rather than in code to get the results intended and with parameters.

    I would think a JOIN would be better than a UNION as a JOIN does what it says, joins several tables together which in turn can be populated into a DataTable. Now the second part is there is zero need for a DataAdapter unless you plan on pushing changes back to the database and if so the DataAdapter needs to be private to the form, not to the procedure loading data.

    JOINS

    https://support.office.com/en-us/article/left-join-right-join-operations-ebb18b36-7976-4c6e-9ea1-c701e9f7f5fb

    Use a LEFT JOIN operation to create a left outer join. Left outer joins include all of the records from the first (left) of two tables, even if there are no matching values for records in the second (right) table.

    Use a RIGHT JOIN operation to create a right outer join. Right outer joins include all of the records from the second (right) of two tables, even if there are no matching values for records in the first (left) table.

    For example, you could use LEFT JOIN with the Departments (left) and Employees (right) tables to select all departments, including those that have no employees assigned to them. To select all employees, including those who are not assigned to a department, you would use RIGHT JOIN.

    The following example shows how you could join the Categories and Products tables on the CategoryID field. The query produces a list of all categories, including those that contain no products:

    SELECT CategoryName, ProductName FROM Categories LEFT JOIN Products ON Categories.CategoryID = Products.CategoryID;

    In this example, CategoryID is the joined field, but it is not included in the query results because it is not included in the SELECT statement. To include the joined field, enter the field name in the SELECT statement — in this case, Categories.CategoryID.

    To create a query that includes only records in which the data in the joined fields is the same, use an INNER JOIN operation.

    • A LEFT JOIN or a RIGHT JOIN can be nested inside an INNER JOIN, but an INNER JOIN cannot be nested inside a LEFT JOIN or a RIGHT JOIN. See the discussion of nesting in the INNER JOIN topic to see how to nest joins within other joins.
    • You can link multiple ON clauses. See the discussion of clause linking in the INNER JOIN topic to see how this is done.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Sunday, February 2, 2020 12:31 AM
    Moderator