Very hard query

• • Reply

  15 years ago

  by jborges

  Julio Borges

  Rio de Janeiro - Brazil, Brazil

  Joined 15 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
  

  
  
  
  
  
  

  Report abuse
• • Reply

  15 years ago

  by sync_or_swim

  Rob Bickel

  S. Wales, United Kingdom

  Joined 15 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         

  Report abuse
• • Reply

  15 years ago

  by sallu_shan

  Arsalan Tamiz

  Karachi, Pakistan

  Joined 15 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.

  Report abuse
• • Reply

  15 years ago

  by ashutosh_dasgupta

  Ashutosh S Dasgupta

  Bangalore, India

  Joined 15 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.
  

  Report abuse
• • Reply

  15 years ago

  by jborges

  Julio Borges

  Rio de Janeiro - Brazil, Brazil

  Joined 15 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
  
  
  
  
  
  
  
  

  Report abuse
• • Reply

  15 years ago

  by jborges

  Julio Borges

  Rio de Janeiro - Brazil, Brazil

  Joined 15 years ago

  I indeed wrote the problem and I just posted in VB database thread.
  
  Thanks to you all.
  Julio Borges
  
  
  
  
  

  Report abuse