January 2, 2013 at 11:35 am
Hello everyone,
I need some help with a query.
I have a table that stores some data. There are two fields that store an ID number, FSDirector and FSMarshall but both of them reference back to a single table using the ID to get a name. so for example, my table's data might look something like this
OccurrenceID, FSDirector, FSMarshall
1, 4, 5
What I want the query to display
OccurrenceID, FSDirector, FSMarshall
1, John Doe(name in FSDirector's table with ID 4), Mike Doe (name in FSDirector's table with ID 5)
So in a table called FSDirectors I have an ID field and and name field
Now I'm having the issue in my query where I need to look up the 2 different ID's in the FSDirectors table in the same row and then pull their names from the FSDirectors table. When I use a inner join I can't get it to join on both fields. if I try:
inner join FSDirectors on OccurranceReport.Employee = FSDirectors.empid AND OccurranceReport.FireMarshall= FSDirectors.empid
The query doesn't return any data, If I try to have it as a separate inner join it complains since I already joined the table.
I also tried using a subquery but it complained about returning more than one row.
Here is my query that has the sub query
Select OccurrenceReport.OccurrenceDate,OccurrenceReport.TimeOfOccurrence,OccurrenceReport.Floor,OccurrenceReport.Description,OccurrenceReport.Injured,OccurrenceReport.FDNY,OccurrenceReport.FalseActual,OccurrenceReport.FPManager,OccurrenceReport.DateSigned,OccurrenceCauses.Cause,FSDirectors.LastName + ', ' + FSDirectors.FirstName as "EmployeeName",(Select FSDirectors.FirstName + ' ' + FSDirectors.LastName as EmpName from FSDirectors inner join OccurrenceReport on FSDirectors.EmpID=OccurrenceReport.FireMarshall where FSDirectors.EmpID=OccurrenceReport.FireMarshall) as "FireMarshall" from OccurrenceReport inner join FSDirectors on OccurrenceReport.EmployeeName=FSDirectors.EmpID inner join OccurrenceCauses on OccurrenceReport.cause=OccurrenceCauses.CauseID inner join Buildings on OccurrenceReport.Building=Buildings.BuildingID
Here is the query without the subquery
Select OccurrenceReport.OccurrenceDate,OccurrenceReport.TimeOfOccurrence,OccurrenceReport.Floor,OccurrenceReport.Description,OccurrenceReport.Injured,OccurrenceReport.FDNY,OccurrenceReport.FalseActual,OccurrenceReport.FPManager,OccurrenceReport.DateSigned,OccurrenceCauses.Cause,FSDirectors.LastName + ', ' + FSDirectors.FirstName as "EmployeeName" from OccurrenceReport inner join FSDirectors on OccurrenceReport.EmployeeName=FSDirectors.EmpID inner join OccurrenceCauses on OccurrenceReport.cause=OccurrenceCauses.CauseID inner join Buildings on OccurrenceReport.Building=Buildings.BuildingID
Here is the query that has the AND that returns no data
Select OccurrenceReport.OccurrenceDate,OccurrenceReport.TimeOfOccurrence,OccurrenceReport.Floor,OccurrenceReport.Description,OccurrenceReport.Injured,OccurrenceReport.FDNY,OccurrenceReport.FalseActual,OccurrenceReport.FPManager,OccurrenceReport.DateSigned,OccurrenceCauses.Cause,FSDirectors.LastName + ', ' + FSDirectors.FirstName as "EmployeeName" from OccurrenceReport inner join FSDirectors on OccurrenceReport.EmployeeName=FSDirectors.EmpID AND OccurrenceReport.FireMarshall=FSDirectors.EmpID inner join OccurrenceCauses on OccurrenceReport.cause=OccurrenceCauses.CauseID inner join Buildings on OccurrenceReport.Building=Buildings.BuildingID
So I basically need to add another field to the query that will use the field Occurrence.FireMarshall and then pull the record with that ID in the FSDirectors table and display the person's name instead of ID number
Thanks in advance
January 2, 2013 at 12:03 pm
Patrick, look up Table Aliases in Books Online. You can indeed join the same table multiple times, but you can't have two tables with the same naming reference.
Table Aliases should explain it all. If it doesn't, feel free to ask more specific questions once you've read the entry.
January 2, 2013 at 12:14 pm
SELECT
OccurrenceReport.OccurrenceDate,OccurrenceReport.TimeOfOccurrence,OccurrenceReport.Floor,OccurrenceReport.Description,
OccurrenceReport.Injured,OccurrenceReport.FDNY,OccurrenceReport.FalseActual,OccurrenceReport.FPManager,
OccurrenceReport.DateSigned,OccurrenceCauses.Cause,
Emp.LastName + ', ' + Emp.FirstName as "EmployeeName",
FireM.LastName + ', ' + FireM.FirstName as "FireMarshall"
FROM OccurrenceReport
INNER JOIN FSDirectors AS Emp ON OccurrenceReport.EmployeeName=FSDirectors.EmpID
INNER JOIN FSDirectors AS FireM ON OccurrenceReport.FireMarshall=FSDirectors.EmpID
INNER JOIN OccurrenceCauses ON OccurrenceReport.cause=OccurrenceCauses.CauseID
INNER JOIN Buildings ON OccurrenceReport.Building=Buildings.BuildingID
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
January 2, 2013 at 12:17 pm
Brandie Tarvin (1/2/2013)
Patrick, look up Table Aliases in Books Online. You can indeed join the same table multiple times, but you can't have two tables with the same naming reference.Table Aliases should explain it all. If it doesn't, feel free to ask more specific questions once you've read the entry.
Thanks Brandie! I got it 🙂
It's been a while that I've developed in SQL Server. I've been doing more front end dev lately. I used the alias and it worked great!
Thanks again!
Here was my end result query
Select OccurrenceReport.OccurrenceDate,OccurrenceReport.TimeOfOccurrence,OccurrenceReport.Floor,OccurrenceReport.Description,OccurrenceReport.Injured,OccurrenceReport.FDNY,OccurrenceReport.FalseActual,OccurrenceReport.FPManager,OccurrenceReport.DateSigned,OccurrenceCauses.Cause,FD.LastName + ', ' + FD.FirstName as "EmployeeName", FM.LastName + ', ' + FM.FirstName as "FireMarshall" from OccurrenceReport inner join FSDirectors as FD on OccurrenceReport.EmployeeName=FD.EmpID inner join FSDirectors as FM on OccurrenceReport.FireMarshall=FM.EmpID inner join OccurrenceCauses on OccurrenceReport.cause=OccurrenceCauses.CauseID inner join Buildings on OccurrenceReport.Building=Buildings.BuildingID
January 2, 2013 at 12:19 pm
So you're getting the data you need now?
January 2, 2013 at 12:24 pm
Brandie Tarvin (1/2/2013)
So you're getting the data you need now?
Yes I am 🙂 Thanks again for your help!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply