Help With Left Joins

[DaveyJ]

Hi Guys,



Need some help with a left join query and have been pulling my hair out.

i have a database with 4 tables

employee details

Software REQ
Hardware REQ
VoiceREQ

Relationship between the tables is the employeeID

I need to show the name and employee ID of the employee, all software , hardware , voice requests , of each employee, based on their employeeID

Can someone point me in the right direction of writing the query so i don't get any duplicate records back

thanks in advance

[jzwp11]

I am curious as to why you would even need a left join in the first place. Assuming that there is a one-to-many relationship between the employee table and each of the request tables, you should not have any duplicates. Am I missing something?

Out of curiosity why not have all requests in one table (like data in 1 table) and the just have a field that identifies the type of request (software, hardware, voice etc.)

tblEmployee
-pkEmpID primary key, autonumber
-txtFName
-txtLName

tblRequestTypes
-pkReqTypeID primary key, autonumber
-txtRequestTypeName

tblEmployeeRequests
-pkEmpReqID primary key, autonumber
-fkEmpID foreign key to tblEmployee
-fkReqTypeID foreign key to tblRequestTypes
-txtRequestDetail
-dteRequest

[DaveyJ]

there is a one-to-many relationship between the employee table and each of the request tables

when i write the query,

if there are more voice requests than software or harware or vice versa, the results are duplicated mutiple of times

[jzwp11]

The other option is to do 3 separate queries and then put them together in a UNION query. A UNION query has to be done in the SQL view and each query must return the same number of fields.


SELECT...
FROM

UNION

SELECT...
FROM

UNION

SELECT...
FROM

[DaveyJ]

would this rule out duplicates?

there may not be the same amount of requests for each table.


if i was to take up you suggesstion with the 1 request table .

is there a way i could use a look up to have 3 types , hardware, software, and voice, but each to have a number of products ,

so if the hareware type is selected the product field would only show hardware products?

[evander]

You may also consider choosing between UNION and UNION ALL.

[DaveyJ]

this is my query, how would you write it to stop duplicates?

SELECT tbl_employee_details.SOEID, tbl_employee_details.FirstName, tbl_employee_details.Surname, tbl_requests.RequestID, tbl_hardware_requests.HardwareType, tbl_hardware_requests.HardwareRequestNumber, tbl_software_requests.ApplicationName, tbl_software_requests.SoftwareRequestNumber, tbl_voice_requests.VoiceRequestType, tbl_voice_requests.VoiceRequestNumber
FROM (((tbl_employee_details INNER JOIN tbl_requests ON tbl_employee_details.SOEID = tbl_requests.SOEID) INNER JOIN tbl_voice_requests ON (tbl_requests.RequestID = tbl_voice_requests.RequestID) AND (tbl_employee_details.SOEID = tbl_voice_requests.SOEID)) LEFT JOIN (tbl_hardware_requests LEFT JOIN tbl_hardware_types ON tbl_hardware_requests.HardwareType = tbl_hardware_types.HardwareTypeID) ON tbl_requests.RequestID = tbl_hardware_requests.RequestID) LEFT JOIN (tbl_software_requests LEFT JOIN tbl_Software_types ON tbl_software_requests.ApplicationName = tbl_Software_types.ApplicationTypeID) ON tbl_requests.RequestID = tbl_software_requests.RequestID
WHERE (((tbl_employee_details.SOEID)=[enter soeid));

[jzwp11]

I see you have tblRequests; that table was not mentioned in your initial post. What is it? Also, you said that you were joining the employee table to each request type table, but that is not what the query shows, you are joining to the employee detail table. How many detail records are there per employee? If there is more than one, then that is probably from where your dupes are coming. Can you post your whole table structure along with the type of data that each holds?

[DaveyJ]

sorry i posted wrong query

SELECT
tbl_employee_details.SOEID,
tbl_employee_details.FirstName,
tbl_employee_details.Surname,
tbl_employee_details.StartDate,
tbl_hardware_requests.HardwareType,
tbl_hardware_requests.HardwareRequestNumber,
tbl_software_requests.ApplicationName,
tbl_software_requests.SoftwareRequestNumber,
tbl_voice_requests.VoiceRequestType,
tbl_voice_requests.VoiceRequestNumber
FROM ((tbl_employee_details INNER JOIN tbl_hardware_requests ON tbl_employee_details.SOEID = tbl_hardware_requests.SOEID)
LEFT JOIN tbl_software_requests ON tbl_employee_details.SOEID = tbl_software_requests.SOEID)
LEFT JOIN tbl_voice_requests ON tbl_employee_details.SOEID = tbl_voice_requests.SOEID
WHERE (((tbl_employee_details.SOEID)=[Enter SOEID]));

[jzwp11]

Because you can have a variable number of requests in each request table for an employee, the number of records returned by the query is dependent on the largest number of records for an employee in a particular request table.

The only solution with your present table structure is using a UNION query as follows:

SELECT tbl_employee_details.SOEID, tbl_employee_details.FirstName, tbl_employee_details.Surname, tbl_employee_details.StartDate, tbl_hardware_requests.HardwareRequestNumber, tbl_hardware_requests.HardwareType
FROM tbl_employee_details INNER JOIN tbl_hardware_requests ON tbl_employee_details.SOEID = tbl_hardware_requests.SOEID
WHERE (((tbl_employee_details.SOEID)=[Enter SOEID]))
UNION
SELECT tbl_employee_details.SOEID, tbl_employee_details.FirstName, tbl_employee_details.Surname, tbl_employee_details.StartDate, tbl_software_requests.SoftwareRequestNumber, tbl_software_requests.ApplicationName
FROM tbl_employee_details INNER JOIN tbl_software_requests ON tbl_employee_details.SOEID = tbl_software_requests.SOEID
WHERE (((tbl_employee_details.SOEID)=[Enter SOEID]))
UNION
SELECT tbl_employee_details.SOEID, tbl_employee_details.FirstName, tbl_employee_details.Surname, tbl_employee_details.StartDate, tbl_voice_requests.VoiceRequestType, tbl_voice_requests.VoiceRequestNumber
FROM tbl_employee_details INNER JOIN tbl_voice_requests ON tbl_employee_details.SOEID = tbl_voice_requests.SOEID
WHERE (((tbl_employee_details.SOEID)=[Enter SOEID]));



I would still recommend a structure with 1 request table since it appears that the fields in each of your 3 current request tables appear to be similar. The general rule is that if tables have similar fields and thus hold similar data, then the data should be in 1 table.

tbl_employee_details
-SOEID
-FirstName
-Surname
-StartDate

tblEmployeeRequests
-pkEmpRequestID primary key, autonumber
-SOEID foreign key to tbl_employee_details
-fkRequestTypeID foreign key to tblRequestTypes (a field to distinquish the type of request)
-RequestNumber
-RequestDate
-ItemRequested


tblRequestTypes (3 records: hardware, software, voice)
-pkRequestTypeID primary, key autonumber
-txtRequestType

[DaveyJ]

using your union query only brings the results in the hardware requests table

[DaveyJ]

i'm still a novice at databases.

the only reason why i used 3 requests tables, was i couldn't figure out how to do a lookup the way i wanted.

what i wanted to do was

have one request table with 3 types of requests, voice, software, and hardware

and each type of requests with a lists of products....

so if software is selected from a dropdown, only the software products would be showing in the products dropdown

does that make sense?

[jzwp11]

I actually built a mock up of your database and the union query worked fine with the exception of the column headers (it used only the hardware header). That can be easily fixed with alias names for the fields, but I am more concerned with your table structure. The table structure takes precedence, so you have to build the forms around the correct table structure, not build the tables around how you want the forms to work.

What you had hoped to do is definitely possible, so lets get your table structure fixed up first.

One key question, can a request consist of multiple items or can there be only 1 item per request?

[DaveyJ]

thanks for your help


1 employee can have mutiple requests of software , voice , hardware, each of these can have mutiple products but each has it's own request number.

[jzwp11]

OK, so only 1 item per request, correct?

If so then I would go with a structure similar to this:

tblEmployee
-pkEmpID primary key, autonumber
-txtFName
-txtLName

tblRequestTypes (3 records: software, hardware, voice)
-pkReqTypeID primary key, autonumber
-txtRequestTypeName

tblEmployeeRequests
-pkEmpReqID primary key, autonumber
-fkEmpID foreign key to tblEmployee
-fkReqTypeID foreign key to tblRequestTypes
-fkItemID foreign key to tblItems
-dteRequest

tblItems
-pkItemID primary key, autonumber
-txtItemName
-fkReqTypeID foreign key to tblRequestTypes (we can use the same categories of software, hardware and voice to categorize the various items. Then when you create a request and specify the request type you can restrict a combo box to only items of that category)