Not sure how to describe 1

[Gatorajc]

I have two tables structured like this.

Projects Departments
ProjID PK DeptProjID PK
ProjName ProjID FK
DeptProjName

Data in them looks like this

Projects

ProjID ProjName
1 Printer Repair
2 Installs
3 Moves

Departments

DeptProjID ProjID DeptProjName

1 0 Development
2 3 <null>
3 1 <null>
4 0 Maintain

I want to get this result

Project ID Project Name
1 Development
2 Moves
3 Printer Repair
4 Maintain

Oh almost forgot this is on SQL Server 7.0.

I can do simple queries but I can not figue this one out.



AJ

 

[rac2]

This oughta work.

SELECT DeptProjID, DeptProjName AS "Project Name"
FROM Departments
WHERE ProjID = 0

UNION ALL

SELECT DeptProjID, ProjName AS "Project Name"
FROM Departments d
JOIN Projects p ON d.ProjID = p.ProjID
WHERE d.ProjID <> 0

ORDER BY DeptProjID

May I add an observation. Departments.ProjID is not a foreign key referring to ProjID in Projects. Why not? Well it is, but it is also a flag that signals whether the row is a department-only type of project, or a general type. You might say that Departments.ProjID is sometimes a foreign key.

And the table named Departments seems to be about DepartmentProjects rather than Departments. Are there actually several different departments? Are they described in your database?

Why not just have two tables?
Projects(ProjID, ProjName)
DepartmentProjects(ProjID, ProjName)

Is there any meaning attached to DeptProjID?

 

[Gatorajc]

Worked great. Learned something new.

At first when I read your questions I was kind of confused but once I went back and read my own post I see what you mean. I changed the table names a little for security reasons(I am pretty picky about that). So that is why they do not make sense. Departments is really more about Department projects so it is below projects in the structure. It all makes sense in the table sturcture.

Thanks again.


AJ