Hi,

I have two tables: the first one has all details about persons, the
second associate the id of each person to the id of his team leader
(id_boss). Bosses themselves are present in the first table

DETAILS_TBL
id_person
first_name
name

PERSON_IN_GROUP_TBL
id_person
id_boss

I must make a query to have this result

[employee] - [boss]
john - smith - jack - doe
michael - ross - jack - doe
jack - doe - jack - doe
tom - cruise - al - pacino

etc

I use mysql and postgresql but I can't imagine a sql statement to do
this.

Anyone can help me?
:-)

BR,

Filippo

<filippo2991 (AT) virgilio (DOT) it> wrote

This is very simple SQL. Almost beginner level.
You need three source tables... The person_in_group_tbl once, and the
details_tbl twice. In order to be able to talk about columns, you have to
use different table aliases for the two instances of the details table.



The answer is something like this:

select
emp.first_name as employee_first,
emp.name as employee_name,
boss.first_name as boss_first,
boss.name as boss_name
from
details_tbl as emp,
person_in_group_tbl as p,
details_tbl as boss
where
emp.id_person = p.id_person and
boss.id_person = p.id_boss

You may need to clean up the column headers, but you get the idea.

David Cressey ha scritto:

fine, I have to digest this :-).
My problem is that I have to implement a search query like

SELECT

(ALL THE QUERY YOU SHOWN)

WHERE

details_tbl.first_name = 'john'

How can I do?

Can I do like this?

SELECT
details_tbl.name
details_tbl.born
FROM
(YOUR PREVIOUS QUERY)
WHERE
details_tbl.first_name = 'john'

filippo wrote:
What will happen if you add your predicate (details_tbl.first_name =
'john') to the where clause of Davids query?

[...]

/Lennart