> > My application tracks employees and their skills. An employee can have any
> > number of skills, including zero. Each skill is made up of several fields,
> > include a Title. There are two tables to represent this relation,
> > tblEmployees and tblEmployeeSkills. There is a EmployeeID field in the
> > Skills table that can be used to get all the skill records pertaining to a
> > particular employee.
> > I need to present the user with a list of employees and their titles. In
> > this view, each employee name must appear at least once with no title, and
> > again for each title he has. The list might look like this:
> > Joe
> > Joe Truck Driver
> > Joe Painter
> > Joe Sculptor
> > Mary
> > Mary Chef
> > John
> > Harry Truck Driver
> > Harry Chef
> > Is there an SQL query that will print out each name once and then once
> > again for every title s/he posseses?
> Hmmm, interesting. I think the displaying of the name alone, even if the
> employee has skills, could become a problem. If this would not be the case,
> it would simply be:
> SELECT DISTINCTROW Employee.Name, Skills.Description FROM Employee LEFT JOIN
> Skills On Employee.ID=Skills.EmployeeID
> This would present you with a list in wich all employees are present.
> Employees without skills will have a Description field with the value Null,
> Employees with one skill will have one entry with the skill field set to
> that one skill, and employees with more than one skill will have an entry
> for every skill they have.
> I don't know how to create an empty entry for each employee first, without
> adding empty entries to the Skill-table for each employee.
There's a table missing in this design. As it currently stands, there's
really no point in having a separate skills table since it would appear
that each skill may only have one employee associated with it. So you
may as well put all the skill info straight into the employee row.
This is clearly 'wrong' since, in the 'real world' [SMTRW! - Dijkstra],
skills are objects with lives of their own, independent of any people
who might happen to have them. You *should* have a skills table, but
you *should not* have an employeeid in such a table. Instead this
should be in the third table which comprises only two columns, an
employeeid and a skillid. Both of these ids should be construed as
the (composite) primary key and each id, individually, should be
indexed with duplicates allowed. You then introduce references (i.e.
referential integrity rules) which permit a one-to-many map from
the employees table to this third table, and a one to many map from
the skills table to this third table.
Thus, one skill may be related to many employees (including *all*
employees - specifically a *single* 'empty' skill can be so related
to all employees) and one employee may be related to many skills.
This is how we model 'real' objects (i.e. people and skills) and
'real' relations between them (i.e. a general many-to-many map
between people and skills). This explanation is probably a lot
'harder' than just doing it - it really is a doddle.
(Oh, btw, once you've got this third table [which I've called
EmployeeSkills, to correlate the Employees table with a Skills
table] in, the sql'd look something like this:
SELECT DISTINCTROW Employees.Name, Skills.Title
FROM Skills INNER JOIN (Employees INNER JOIN EmployeeSkills ON
Employees.EmployeeID = EmployeeSkills.EmployeeID) ON
Skills.SkillID = EmployeeSkills.SkillID
Now, Andre''s idea of using an outer join works well, since you
don't actually need a blank skill any more [much less the rather
horrible blank skill for each employee]. Simply change the first
inner join to a right join [so that *all Employees* are included,
even if they don't have any entries in EmployeeSkills, i.e. they've
no skills] and the second inner join to a left join [so that *only
Skills* referenced by the map table are included].
Paul Sampson____Principal Consultant Onyx Systems______Onyx Internet
Zetland Buildings__Exchange Square__Middlesbrough__TS1 1DE__UK 33711 \\/+
PGP:BB74A4EF 03F844C1 F375FEC6 7EF96E43 http://tradezone.onyx.net >--<