June 22, 2011 at 10:51 am
I have the following query:
SELECT
E.FirstName, E.LastName,
E.HiredDate,
T.TerminationDate
FROM
Employee AS E LEFT OUTER JOIN
Termination AS T ON E.EmployeeID = T.EmployeeId
All the rows are returned as I expect, but the TerminationDate is always null even though there are entries in the Termination table.
What am I doing wrong?
Thanks
June 22, 2011 at 11:40 am
The LEFT Join means it will return every employee row regardless if there is a match in the T.Terminations table. If there is a match then it will show the date of termination. Is it possible that all terminated employees have already been deleted from the employee table?
June 22, 2011 at 12:16 pm
Found my mistake.
It should be E.ID = T.EmployeeID
NOT
E.EmployeeId = T.EmployeeID
Thanks.
June 22, 2011 at 12:55 pm
bubs (6/22/2011)
Found my mistake.It should be E.ID = T.EmployeeID
NOT
E.EmployeeId = T.EmployeeID
Thanks.
Perfect example of why column names should remain the same across tables. Otherwise you can end up with a column in two tables that don't mean the same thing. YIKES!!!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 23, 2011 at 3:25 am
Have IntelliSense enabled to avoid such problems.
June 23, 2011 at 3:36 am
June 23, 2011 at 7:33 am
And intellisense can not make up for naming issues like the OP is facing. That is a mistake that anyone with any real sense would make because the join has to be made on columns with dissimilar names when both tables have columns with the same name. Hope they have a good data dictionary.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 24, 2011 at 12:58 pm
Just to add my 2 cents to what Sean posted. You should think of column names as objects in the database. The Employee table shouldn't have a column called ID - it should be EmployeeID. That was you don't get confused in the Salesman table when you also have EmployeeID, or the DepartmentHead table when you have EmployeeID.
Think of EmployeeID as an object that has a name and it's the same everywhere.
Todd Fifield
June 24, 2011 at 1:03 pm
Thanks for all the feedback guys! Helped me alot!
June 25, 2011 at 4:45 pm
tfifield (6/24/2011)
Just to add my 2 cents to what Sean posted. You should think of column names as objects in the database. The Employee table shouldn't have a column called ID - it should be EmployeeID. That was you don't get confused in the Salesman table when you also have EmployeeID, or the DepartmentHead table when you have EmployeeID.Think of EmployeeID as an object that has a name and it's the same everywhere.
Todd Fifield
I agree that it's more intuitive to have EmployeeID everywhere, including in the Employee table. Although Celko may agree with a column name of EmployeeID he may disagree with a table name of Employee in the first place...maybe Personnel 🙂
In any case, not sure I would complain about a table named Employee, but I do take issue with columns named simply ID. I tried unsuccessfully to explain the concept to a few ORM folks at one stop and I was amazed at the amount of opposition there was towards what seemed like a very sensical naming convention (at least to me). Note they asked me to review some performance issues so it was kind of a Monday Morning Quarterback situation, there was really no chance of the naming convention changing for that particular database. It came out in the conversation that they had a base object in their ORM layer that contained an ID column that all other objects inherited from so naturally, to make their lives easier, they added a column named ID to every table in the database instead of using something like TableNameID.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 26, 2011 at 2:00 am
Couldn't agree more
June 26, 2011 at 2:17 pm
opc.three (6/25/2011)
tfifield (6/24/2011)
Just to add my 2 cents to what Sean posted. You should think of column names as objects in the database. The Employee table shouldn't have a column called ID - it should be EmployeeID. That was you don't get confused in the Salesman table when you also have EmployeeID, or the DepartmentHead table when you have EmployeeID.Think of EmployeeID as an object that has a name and it's the same everywhere.
Todd Fifield
I agree that it's more intuitive to have EmployeeID everywhere, including in the Employee table. Although Celko may agree with a column name of EmployeeID he may disagree with a table name of Employee in the first place...maybe Personnel 🙂
In any case, not sure I would complain about a table named Employee, but I do take issue with columns named simply ID. I tried unsuccessfully to explain the concept to a few ORM folks at one stop and I was amazed at the amount of opposition there was towards what seemed like a very sensical naming convention (at least to me). Note they asked me to review some performance issues so it was kind of a Monday Morning Quarterback situation, there was really no chance of the naming convention changing for that particular database. It came out in the conversation that they had a base object in their ORM layer that contained an ID column that all other objects inherited from so naturally, to make their lives easier, they added a column named ID to every table in the database instead of using something like TableNameID.
Don't you just love ORM tools! I have to work on a database where the column names are generated and NEVER the same between tables. Each column name is prefaced by a code for the table.
For instance the column name for OrderID in the Order table is: fldOrd_OrderID. In the OrderDetail table it's: fldOD_OrderID. There's no copy/paste of column names and you have to really eyeball each one to make sure you got it right. Ouch!! Also, all tables have a clustered PK that's a GUID. Double Ouch!!
Todd Fifield
June 26, 2011 at 7:51 pm
I do love them, like hangnails, and splinters 😀
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply