Very Computer

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?

Quote:>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?

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.

Andre'

Your SQL would then be simply something like :

SELECT TblEmployees.employeeName, TblEmployeeSkill.employeeSkill ;
       FROM TblEmployees, TblEmployeeSkill ;
       WHERE TblEmployee.EmployeeID = TblEmployeeSkill.EmployeeID ;
       ORDER BY employeeName, employeeSkill

In the report, group data on EmployeeName

Hoping this helps

Christian Desbourse (Belgium)

Try:
SELECT Employee.Name, SPACE(20) AS Description ;
FROM Employee ;
UNION ;
SELECT Employee.Name, Skills.Description ;
FROM Employee JOIN Skills ON Employee.ID = Skills.EmployeeID ;
ORDER BY 1, 2

This would negate the need for the LEFT JOIN.

--

James H. Blythe
TCOSS Team Leader

I'm assuming that you're querying FoxPro tables. I'm also using 20 as the
length of the title field in this example. Try this:

SELECT emp.Name, skill.Title ;
    FROM emp,skill ;
    WHERE emp.ID=skill.EmpId ;
UNION ;
SELECT emp.Name, SPACE(20) AS Title ;
    FROM emp ;
    ORDER BY 1,2

--
Josh Weiss
Boston Microcomputer Consulting

http://emporium.turnpike.net/~bmc

Remove XXX from e-mail address when replying

Quote:>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?

--
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 >--<

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 >--<

Select NAME, ' <a bunch of blanks>   ' from
employee_list
UNION ALL
select NAME, SKILL from employee_list a,
skill_list b
where a.name=b.name
order by 1

This first part gives just the name.  The second
gives the name with the skill wherever there is a
join.  Does this work for you?

If you are using VFP 5.0x you can use a left outer join to accomplish your
task. I as you want all employees no matter if they have any skills or not.

SELECT emp.name, skill.title ;
    FROM emp LEFT OUTER JOIN skill ;
        ON emp.id = skill.empid ;
INTO CURSOR test

If that are any other conditions put them in a WHERE clause after the FROM.

Mike