Select 3 times two colums in mysql query

[developer707]

Hello,
I have two tables one for the company staff and one for their projects(table name objecte).
The table objekte has 4 colums that are connected with the id of the staff.
The problem is that I want a query that shows all staff's names.

I have also included the sql of the tables in this post and a preview of what I am going to do.

Can someone help me?

[pritaeas]

Start with this, you just need to select the columns you need:

select *
from objektet o
left join stafi s1
  on s1.id = o.id_pergjegjesi
left join stafi s2
  on s2.id = o.id_punime_civile
left join stafi s3
  on s3.id = o.id_punime_elektomekanike
left join stafi s4
  on s4.id = o.id_punime_elektrike

[developer707]

Thank you priaeas!
I will try to make it works in my case, because the execution of this query gives me to many colums for the moment ...

[pritaeas]

You can just select what you need from the o and s1 aliases.

Hello,

are you about to design a new database or have you been working on an already existing database which include these two tables stafi and objektet?

Well, if you are freely able to change both tables, life could be easier.

Consider this:

A staff member is working on many projects.
A project has many staff members working on it.

I believe there is a many-to-many relationship between both tables stafi and objektet. If so, the standard implementation consists of three tables: stafi, objektet, and a connection table what connects both tables. This connection table must contain at least both primary keys of the first two tables.

So it would be better to implement the standard solution if there is a true many-to-many relationship.
Btw, do you know the Entity Relationship Model by Chen?

I also suggest never suppress foreign key checks, even if one uses MySQL.

-- 1stDAN