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.
Start with this, you just need to select the columns you need:
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
You can just select what you need from the o and s1 aliases.
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.
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.