Very hard query

vb6 Kuwait
  • 11 years ago

    I have to place a query, which returns only one line, in one table where the same employee could be found in two lines (he/she has two employee numbers (two different companies)). Naturally he has the very same name and could have two different careers (like doctor and teacher).

     

    Employee Joe Smith Employee number 21 job doctor

    Employee Joe Smith Employee number 22 job teacher

     

    Dim cTemp as string

    cTemp = “Joe Smith”

    Select employee, number-1, job-1, number-2, job-2 from employees where employee =  cTemp

     

    That’s my problem: how can I catch  two different numbers and jobs in two different lines.

     

    Any suggestions???

    Best Regards,
    Julio Borges
    Rio de Janeiro - Brazil






  • 11 years ago

    Hi Julio,

    I'm not sure I fully understand your problem!!

    If you add a module to your project and declare a structure:

    Option Explicit

    'Record Structure
    Public Type employeerecord
    empname(10) As String
    empnumber(10) As String
    empjob(10) As String



    End Type

    In your code block add:

    Dim emprecord as employeerecord
    Dim counter as Integer

    counter = 0

    sqlstring = "select employee, number, job from employees where employee =  '" & cTemp & "'"
    Set rstRecordSet = conconnection.Execute(sqlstring)
        While Not rstRecordSet.EOF
              If Not IsNull(rstRecordset!employee) Then emprecord(counter).empname = rstRecordset!employee


             If Not IsNull(rstRecordset!number) Then emprecord(counter).empnumber = rstRecordset!number

             If Not IsNull(rstRecordset!job) Then emprecord(counter).empjob = rstRecordset!job

           If Not rstRecordset.EOF Then

                   rstRecordset.MoveNext

                   counter = counter + 1

          End If

    Wend  

     This should loop thru your recordset and find every occurence of your search criteria person and put their details in a structure which can be looped thru.

    The thing I dont understand is why the same person would be in your database twice with different jobs???

    Hope this helps,

    Bob         

  • 11 years ago

    If you are trying to print 2 different records in same line as,

    ID-1       Name-1             Job-1           ID-2               Number-2           Job-2

    -------------------------------------------------------------------------------------

    21          Joe Smith           Doctor          22                  Joe Smith             Teacher 

    Unfortunately this is not posible, but yes you can print in 2 different lines with this query,

    Select * From Employees Where Employee = cTemp

    If this is your requirement to print the records in same line then you can fetch the records using above query and then manually print the all the records in same line using VB code.

  • 11 years ago

    Hi jborges, 

                 Thing u r trying to achieve i don't thing is possible in a query unless and until u r sure
    to have exactly 0 to  2 records for an employee.

    The good solution is to loop, as suggested....

    But still if u want, the above thing can be achieved, but is very restrictive and will not have a good performance. U can use the following query if u are sure to not more than 2 records for an employee.

    SELECT  A.empid, A.empname, A.number N1, A.jobno J1, 

    (Select B.number From Employee B Where A.empid = B.empid and A.jobno <> B.jobno) N2,         (Select C.jobno From Employee C Where A.empid = C.empid and A.jobno <> C.jobno) J2
    FROM Employee A
    WHERE rownum =1

    This is really a wrong way of writing a query, unless and until u gurantee so many things.
    It would be better if we could, select 2 columns in the single query instead of two in the main Select clause.

    Regards,
    ASD.

  • 11 years ago

        Thanks to all that tryed to help me.

    I think now that my real problem is to make a better database design. I'll describe my whole problem and try to understand how can I make a better table design.

    I'll keep you posted.

    Julio Borges







  • 11 years ago

    I indeed wrote the problem and I just posted in VB database thread.

    Thanks to you all.
    Julio Borges




Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“PHP is a minor evil perpetrated and created by incompetent amateurs, whereas Perl is a great and insidious evil perpetrated by skilled but perverted professionals.” - Jon Ribbens