2

I'm doing a java application with a Postgres database and the following schema:

enter image description here

The entity employee, rol, project has some information inside,and the entity participants is empty. I want to show in my application a table of all projects with no leader assigned yet. I'm sure that#s possible with an SQL query but I'm not sure how. I tried this query:

SELECT p.projectnumber from participants pa, projecto p
where p.projectnumber=pa.projectnumber and pa.leaderid IS NULL;

But no rows are returned. That's because the participants entity is empty, but I cannot fill that entity with only the projectnumbers. Do you think I could make it easier with a query or well any other suggestion?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Daniel Esteban Ladino Torres
  • 203
  • 1
  • 8

2 Answers2

1

You can use left join assuming projects which don't have entries in Participants table will be without leader:

SELECT p.projectnumber 
FROM projecto p LEFT JOIN participants pa
ON p.projectnumber=pa.projectnumber 
WHERE pa.leaderid IS NULL;
Harsh
  • 1,309
  • 8
  • 14
1

I want to show on my application a table that shows all the projects with no Leader assigned yet

Guessing that leaders are signified by having a non-null value in participants.leaderid:

SELECT projectnumber
FROM   projecto p
WHERE  NOT EXISTS (
   SELECT 1
   FROM   participants
   WHERE  projectnumber = p.projectnumber
   AND    leaderid IS NOT NULL
   );

You can solve it with a LEFT JOIN as well, but then include the leaderid in the join condition:

SELECT p.projectnumber
FROM   projecto p
LEFT   JOIN participants pa ON pa.projectnumber = p.projectnumber
                           AND pa.leaderid IS NOT NULL
WHERE  pa.projectnumber IS NULL;

The check on leaderid in the WHERE condition (after the LEFT JOIN) cannot distinguish whether the column leaderid is NULL in the underlying table or because there is no connected row in participants at all. In this particular query, the result would still be correct (no participant, no leader). But it would return one row per participant that's not a leader, and I expect you want to list every leader-less project once only. You would have to aggregate, but why join to multiple non-leaders to begin with?

Basics:

That aside, your relational design doesn't seem to add up. What's to prevent multiple leaders for the same project? Why varchar(30) for most columns? Why no FK constraint between participant and project? Why projecto in the query, but project in the ER diagram? Etc.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • well the problem i must design tells me that an employee can be in one or more projects, so i think he can be the leader of many projects. i will take in consideration all your questions, but i can only tell you i started learning the fundamentals of data bases, so i'm still a newbee on this matter. I still have a long path to learn :D – Daniel Esteban Ladino Torres Nov 30 '15 at 01:30
  • @DanielEstebanLadinoTorres: One project can have many employees (as participants), one employee can take part in many projects. Seems like a typical many-to-many relationship. The role played can be considered an additional attribute (simplified). Instructions here: http://stackoverflow.com/a/9790225/939860 – Erwin Brandstetter Nov 30 '15 at 01:35
  • also thanks for correcting the question, my native language is spanish so i migth have a few grammar errors >. – Daniel Esteban Ladino Torres Nov 30 '15 at 01:35