0

I'm a college student and the database i'm working with is purely fictional but part of it requires me to make a query that is a join.

What I have are 3 tables each with part of the data but also needing to use two of those tables as conditions based off the main table. What I mean is I have an employee table, order table and customer table and the only thing any two of them have in common is the ID of either the employee or the customer is part of the order table. Now what i am trying to do is create a join statement that will get certain information from the employee and customer tables and only those that both the employee and the customer are also on the same line in the order table.

How should i make this type of conditional statement? Any example using the same basic scenario will work I can use that to help me build my own query.

This is what i have right now:

SELECT [Customer/Vendor_Info_local].Name_of_customer,
       [Customer/Vendor_Info_local].[Customer/VendorID],
       Employee_Info_local.Employee_Name, 
       Employee_Info_local.EmployeeID
FROM Employee_Info_local 
RIGHT JOIN ([Customer/Vendor_Info_local] 
RIGHT JOIN Order_Information_local 
   ON [Customer/Vendor_Info_local].[Customer/VendorID] = Order_Information_local.[Cusrtomer/VendorID]) 
   ON Employee_Info_local.EmployeeID = Order_Information_local.EmployeeID;
  • Ok changed the sql to look like this now and it still isn't working I get an expression error statement: SELECT [Customer/Vendor_Info_local].Name_of_customer, Employee_Info_local.Employee_Name FROM Employee_Info_local, [Customer/Vendor_Info_local], Order_Information_local WHERE (([Customer/Vendor_Info_local].[Customer/VendorID]=[Order_Information_local].[Cusrtomer/VendorID]) AND ([Employee_Info_local].[EmployeeID]=[Order_Information_local].[EmployeeID])); – Lelon Hood Aug 1 '16 at 6:50
0
SELECT C.Name_of_customer,
       C.[Customer/VendorID],
       E.Employee_Name, 
       E.EmployeeID 
from Order_Information_local as O
JOIN Employee_Info_local as E
JOIN [Customer/Vendor_Info_local] as C
       ON C.[Customer/VendorID] = O.[Cusrtomer/VendorID]) 
      AND E.EmployeeID = O.EmployeeID;
0

only those that both the employee and the customer are also on the same line in the order table

This translates to an Inner Join instead of Outer:

SELECT cust.Name_of_customer,
       cust.[Customer/VendorID],
       emp.Employee_Name, 
       emp.EmployeeID
FROM Employee_Info_local AS emp
JOIN Order_Information_local AS ord
  ON emp.EmployeeID = ord.EmployeeID
JOIN [Customer/Vendor_Info_local] AS cust
  ON cust.[Customer/VendorID] = ord.[Cusrtomer/VendorID]

Two recommendations:

  1. Use table aliases
  2. better join using table JOIN table ON ... JOIN next table ON ... instead of table JOIN table JOIN next table ON ... ON .... Most people prefer this as it's easier to follow/extend (the latter joins the first table in the last on-condition).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.