Help SQL Statement Joining Tables

[networkmancer]

The problem is that it will show 2 the same result but with different employee fname.
How to have something like this .
"Employee.employee_id has employee.fname "

It will show also lots of result if I will not use distinct.

Select distinct TRANSACTION.TRANSACTION_ID ,CUSTOMER.FNAME ,CUSTOMER.LNAME,PET.PNAME,Employee.Emp_ID,Employee.fname,TRANSACTION.DATESTART,TRANSACTION.DATEEND 
                    from(TRANSACTION) 

                  INNER  JOIN CUSTOMER
                   ON TRANSACTION.CUSTOM_ID = CUSTOMER.CUSTOM_ID
                    inner JOIN EMPLOYEE
                   ON TRANSACTION.EMP_ID = EMPLOYEE.EMP_ID
                   INNER  JOIN EMPLOYEE
                   ON TRANSACTION.EMP_ID = EMPLOYEE.EMP_ID
                     INNER JOIN PET
                   ON TRANSACTION.PET_ID = PET.PET_ID    
                                 INNER JOIN EMPLOYEE
                   ON TRANSACTION.EMP_ID = EMPLOYEE.EMP_ID

                   Where TRANSACTION.STATUS=1 

                 order by TRANSACTION.TRANSACTION_ID

TRANSACTION TABLE COLUMNS

TRANSACTION_ID  CAMOUNT COST    CHANGE  CUSTOM_ID   EMP_ID  PET_ID  DATESTART   DATEEND SERVICES    STATUS  ROOMCOST    DATETODAY

EMPLOYEE TABLE COLUMNS

EMP_ID  FNAME   MNAME   LNAME   AGE ADDRESS BDAY    EMPTYPE USER_ID

PET TABLE COLUMNS

PET_ID  PNAME   PCOLOR  PBREED  PGENDER PET_ID  PNAME   PCOLOR  PBREED  PGENDER

CUSTOMER TABLE COLUMNS

CUSTOM_ID   FNAME   MNAME   LNAME   ADDRESS CONTACT_NUM EMAIL   EMP_ID  PET_ID

[networkmancer]

Select  TRANSACTION.TRANSACTION_ID ,CUSTOMER.FNAME ,CUSTOMER.LNAME,PET.PNAME,Employee.Emp_ID,Employee.fname,TRANSACTION.DATESTART,TRANSACTION.DATEEND 
                    from(TRANSACTION) 
       
                  INNER  JOIN CUSTOMER
                   ON TRANSACTION.CUSTOM_ID = CUSTOMER.CUSTOM_ID
                   INNER JOIN PET
                   ON TRANSACTION.PET_ID = PET.PET_ID    
                   INNER JOIN EMPLOYEE
                   ON TRANSACTION.EMP_ID = EMPLOYEE.EMP_ID
                   Where TRANSACTION.STATUS=1 
                    order by TRANSACTION.TRANSACTION_ID

Cant edit my code. This is my code. I forgot to remove repeted Transaction.EMP_ID

[poojavb]

Select  TRANSACTION.TRANSACTION_ID ,CUSTOMER.FNAME ,CUSTOMER.LNAME,PET.PNAME,Employee.Emp_ID,Employee.fname,TRANSACTION.DATESTART,TRANSACTION.DATEEND 
                    from(TRANSACTION) 
       
                  INNER  JOIN CUSTOMER
                   ON TRANSACTION.CUSTOM_ID = CUSTOMER.CUSTOM_ID
                   INNER JOIN PET
                   ON TRANSACTION.PET_ID = PET.PET_ID    
                   INNER JOIN EMPLOYEE
                   ON TRANSACTION.EMP_ID = EMPLOYEE.EMP_ID
                   Where TRANSACTION.STATUS=1 
                    order by TRANSACTION.TRANSACTION_ID

Cant edit my code. This is my code. I forgot to remove repeted Transaction.EMP_ID

Try this code

Select  TRANSACTION.TRANSACTION_ID, CUSTOMER.FNAME, CUSTOMER.LNAME, PET.PNAME, Employee.Emp_ID, Employee.fname, TRANSACTION.DATESTART, TRANSACTION.DATEEND 
from (TRANSACTION INNER  JOIN CUSTOMER
                   ON TRANSACTION.CUSTOM_ID = CUSTOMER.CUSTOM_ID)
                   INNER JOIN PET ON TRANSACTION.PET_ID = PET.PET_ID    
                   INNER JOIN EMPLOYEE ON TRANSACTION.EMP_ID = EMPLOYEE.EMP_ID
                   Where TRANSACTION.STATUS=1 order by TRANSACTION.TRANSACTION_ID

[poojavb]

Select  TRANSACTION.TRANSACTION_ID ,CUSTOMER.FNAME ,CUSTOMER.LNAME,PET.PNAME,Employee.Emp_ID,Employee.fname,TRANSACTION.DATESTART,TRANSACTION.DATEEND 
                    from(TRANSACTION) 
       
                  INNER  JOIN CUSTOMER
                   ON TRANSACTION.CUSTOM_ID = CUSTOMER.CUSTOM_ID
                   INNER JOIN PET
                   ON TRANSACTION.PET_ID = PET.PET_ID    
                   INNER JOIN EMPLOYEE
                   ON TRANSACTION.EMP_ID = EMPLOYEE.EMP_ID
                   Where TRANSACTION.STATUS=1 
                    order by TRANSACTION.TRANSACTION_ID

Cant edit my code. This is my code. I forgot to remove repeted Transaction.EMP_ID

I have tried the below using my tables where

your transaction table is my appointment table
remaining tables are patientreg,doctorregister,treatmentmaster

See the below code...it worked fine for me...

SELECT appt.ApptID,(dr.DoctorID+ " has " +dr.firstname) as Title, tmt.TreatID, pt.PatientID
FROM patientreg AS pt INNER JOIN (treatmentmaster AS tmt INNER JOIN (doctorregister AS dr INNER JOIN appointment AS appt ON dr.DoctorID = appt.DoctorID) ON tmt.TreatID = appt.TreatmentID) ON pt.patientid = appt.PatientID
WHERE (((appt.PatientID)=[pt].[patientid]) AND ((dr.DoctorID)=[appt].[doctorid]) AND ((tmt.TreatID)=[appt].[Treatmentid]));

[networkmancer]

The has part was just my logic on the sql i want to acquire. :) Ill try that now..

[poojavb]

The has part was just my logic on the sql i want to acquire. :) Ill try that now..

If it helps u mark the thread as solved :)

[networkmancer]

Can you convert it for me? I just cant follow the others.

SELECT TRANSACTION.TRANSACTION_ID,(EMPLOYEE.EMP_ID+ " has " +EMPLOYEE.FNAME) as Title, PET.PET_ID, CUSTOMER.CUSTOM_ID
    FROM TRANSACTION AS CUSTOMER 
INNER JOIN (treatmentmaster AS PET)
 INNER JOIN (doctorregister AS EMPLOYEE INNER JOIN appointment AS TRANSACTION ON EMPLOYEE.EMP_ID = TRANSACTION.EMP_ID)
ON PET.PET_ID = TRANSACTION.TreatmentID) ON CUSTOMER.CUSTOM_ID = TRANSACTION.CUSTOM_ID
    WHERE (((TRANSACTION.CUSTOM_ID)=[CUSTOMER].[CUSTOM_ID]) AND ((EMPLOYEE.EMP_ID)=[TRANSACTION].[EMP_ID]) AND ((PET.PET_ID)=[TRANSACTION].[Treatmentid]));