Quote:
Originally Posted by nejo
I wrote it like this:
SELECT DISTINCT No_ as EmployeeID, e.[First Name] as "First Name", e.[Last Name] as "Last Name",
eab."Employee No_"
FROM Employee e ,
[Employee Absence] eab
WHERE e.No_= eab.[Employee No_]
AND eab.[Cause of Absence Code]='SICK'
I believe mine worked, on upon a brief inspection was OK:ed by a tutor(the one who helps the professor out at universities).
What's the difference between how you wrote it and how I wrote it?
I didn't join tables, but instead checked two tables for matching EmpID.
Any thoughts and/or input is appreciated, trying to figure out how all of this works.
The way you did it (two tables in the SELECT list) was deprecated, or at least highly discouraged, in the SQL-92 standard. Somehow 22 years later we still haven't managed to get rid of it. Among other disadvantages, I have to go hunting in the WHERE clause for information on how you intended the tables to be joined. This is OK for a simple query like that, but imagine listing 5 tables in the SELECT and then having like 18 WHERE clauses to look through to figure out how the joins were working. It's way more understandable to specify the join information straight away.
Your tutor should really not be accepting that answer and it kind of smacks of laziness and not wanting to teach you the right way to do things. (The presence of trailing underscores and spaces in the column names of the data sets you were given, while not "wrong", is a bit wtf).
Use of quotes to delimit column or table names in SQL is also non-standard at best. Always use square brackets.
This is how I'd write that query:
SELECT DISTINCT e.No_ EmployeeID, e.[First Name], e.[Last Name]
FROM Employee e
INNER JOIN [Employee Absence] eab ON e.No_= eab.[Employee No_]
WHERE eab.[Cause of Absence Code] = 'SICK'
A few notes about this:
- You don't need to alias column names if they're already called what you want. "e.[First Name] AS [First Name]" doesn't do anything.
- "AS" when aliasing table or column names is optional. You can just put a space in between if you like (which you have done with the tables). It's really just a style thing.
- There's no need to select both e.No_ and eab.[Employee No_] in the SELECT list, as per the join they are always identical values. (Columns don't have to be in the SELECT list to appear in ON or WHERE clauses).
Joins are easy and are usually explained badly:
INNER JOIN means "give me all the combinations of the rows in the two row sets that match the ON clause"
LEFT OUTER JOIN means "the same thing, but for any rows in the left row set which don't appear, add them to the result set with NULLs for all the right table values. I don't want to lose any rows from the left set".
RIGHT OUTER JOIN is the same thing in reverse (keep all right row set rows).
FULL OUTER JOIN adds all the rows that are added by LEFT OUTER JOIN
and all the rows added by RIGHT OUTER JOIN.
That's really it. The rarely seen CROSS JOIN is just a list of all possible combinations of the two row sets. It's exactly equivalent to INNER JOIN ON 2 + 2 = 4, i.e. an ON clause that is always true.
Last edited by ChrisV; 11-08-2014 at 12:04 AM.
