Return all items and indicate if a relationship exists?

Try:
Select * from dbo.ProjectUpgrades left join (dbo.rel_Projects_ProjectUpgrades Inner Join dbo.Projects On dbo.rel_Projects_ProjectUpgrades.projectID = dbo.Projects.projectID)
on dbo.ProjectUpgrades.projectUpgradeID = dbo.rel_Projects_ProjectUpgrades .projectUpgradeID order by dbo.ProjectUpgrades.projectUpgradeID

The query I presented above will return all ProjectUpgrades and the Projects that have upgrades.  
It will not return Projects that do not have upgrades.  To do that, you would want to use a full outer join which would look like:

Select * from dbo.ProjectUpgrades Full Outer join (dbo.rel_Projects_ProjectUpgrades Full Outer Join dbo.Projects On dbo.rel_Projects_ProjectUpgrades.projectID = dbo.Projects.projectID)
on dbo.ProjectUpgrades.projectUpgradeID = dbo.rel_Projects_ProjectUpgrades .projectUpgradeID order by dbo.ProjectUpgrades.projectUpgradeID  

SELECT U.UpgradeID, Count(R.ProjectID) AS CountOfProjectID
FROM Upgrades AS U LEFT JOIN Rel_UpgradesProjects AS R ON U.UpgradeID = R.UpgradeID
GROUP BY U.UpgradeID;

Jammer59,
     I tried something similar to your first query and I still only get the upgrades associated with the projects. I don't get all the upgrades + the projects that may have upgrades.

The rel_Projects_ProjectUpgrades will opnly have values in it if there is a relationship to a project. You probably knew that though.

Also, you secord query only gave back the porjects with upgrades. I am not sure if I am doing something wrong.
Daniel

projectUpgradeID      projectUpgradeName      projectID      projectTitle
1                            Upgrade 1                           88      Test88
1                            Upgrade 1                           91      Test91
1                            Upgrade 1                           87      Test87
2                            Upgrade 2                           91      Test91
3                            Upgrade 3                         91      Test91

I am only getting back projects with upgrades using both quries. There is and Upgrade  of ID 4 that is not beign displayed. There is no relatationship for it yet (that is on purpose so I could see if it comes up). I am attaching my DDL. I thought, perhpas, the FKs were causing an issu. I removed them and still had the same trouble.

DDL:
 
CREATE TABLE [dbo].[rel_Projects_ProjectUpgrades] (
  [projectID] int NOT NULL,
  [projectUpgradeID] int NOT NULL,
  CONSTRAINT [rel_Projects_ProjectUpgrades_projectID] FOREIGN KEY ([projectID]) 
  REFERENCES [dbo].[Projects] ([projectID]) 
  ON UPDATE NO ACTION
  ON DELETE CASCADE,
  CONSTRAINT [rel_Projects_ProjectUpgrades_projectUpgradeID] FOREIGN KEY ([projectUpgradeID]) 
  REFERENCES [dbo].[ProjectUpgrades] ([projectUpgradeID]) 
  ON UPDATE NO ACTION
  ON DELETE CASCADE
)
ON [PRIMARY]
GO
 
 
 CREATE TABLE [dbo].[ProjectUpgrades] (
  [projectUpgradeID] int IDENTITY(1, 1) NOT NULL,
  [projectUpgradeName] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
  PRIMARY KEY CLUSTERED ([projectUpgradeID])
)
ON [PRIMARY]
GO
 
 
 CREATE TABLE [dbo].[Projects] (
  [projectID] int IDENTITY(1, 1) NOT NULL,
  [projectTitle] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
  [tempUserID] int NULL,        
  [dateCreated] datetime DEFAULT getdate() NOT NULL,
  [dateModified] datetime DEFAULT getdate() NOT NULL,
  PRIMARY KEY CLUSTERED ([projectID])  
)
ON [PRIMARY]
GO

Select allOpen in new window

I used your DDL to create the tables.  I moved the CREATE TABLE [dbo].[rel_Projects_ProjectUpgrades] to the bottom because it has constraints that rely on the other two tables (it is a junction table).

I populated the projects table with 5 projects with projectIDS 1,2,3,4,5.  I plan on projectID 5 not having any upgrades.

I populated the projectupgrades table with 4 upgrades with projectUpgradesIDs of 1,2,3,4 and projectUpgradeNames of upgrade1, upgrade2, upgrade3, upgrade4.  I plan on upgrade4 not being associated with any projects.

The rel_Projects_ProjectsUpgrade table looks like this.

ProjectID     UpgradeID
1                     1
1                     2
2                     2
3                     3
4                     3
You can see that the Project with ProjectID 5 does not exist in the above table and the Upgrade with UpgradeID 4 doesn't exist in the above table.
I modifies the query to return less columns.  Here is the new query.

Select ProjectUpgrades.projectUpgradeID, Projects.projectID from dbo.ProjectUpgrades Full Outer join (dbo.rel_Projects_ProjectUpgrades Full Outer Join dbo.Projects On dbo.rel_Projects_ProjectUpgrades.projectID = dbo.Projects.projectID)
on dbo.ProjectUpgrades.projectUpgradeID = dbo.rel_Projects_ProjectUpgrades .projectUpgradeID order by dbo.ProjectUpgrades.projectUpgradeID

Here are the results:

projectUpgradeID    projectID
Null                             5
1                                  1
2                                  1
2                                  2
3                                  3
3                                  4
4                                  Null
This table tells me the project with projectID 5 has no associated upgrades and the upgrade with projectUpgradeID 4 is not associated with any projects.
Does this help?

Select ProjectUpgrades.projectUpgradeID, Projects.projectID from dbo.ProjectUpgrades Full Outer join (dbo.rel_Projects_ProjectUpgrades Full Outer Join dbo.Projects On dbo.rel_Projects_ProjectUpgrades.projectID = dbo.Projects.projectID) 
on dbo.ProjectUpgrades.projectUpgradeID = dbo.rel_Projects_ProjectUpgrades .projectUpgradeID order by dbo.ProjectUpgrades.projectUpgradeID 

Select allOpen in new window

It is more helpfult to see a query where we know the projectID and can see what upgrades are associated and not associated for that project. By the way, the above query didn;t work for me. I even createed a whole new database with jsut those tables. I still only get back Projects associated with upgrades. I have no idea what is going on. I figured just using a left join on the upgrades table would be fine, but i have never seen MSSQL act like this.

The results might look like this:

upgradeID          projectID
1                         88
2                         88
3                         null
4                         null
5                          88

In the above example I would see that upgrades 3 and 4 are not associated with project 88.

Daniel

calpurnia is on the right track. I just need to be able to only show for 1 ProjectID now.

Daniel

Yes, but still show all the Upgrades available. That query only shows the upgrades for a specific project not all upgrades and those associated with the project. I might need to have that be the inner query and the outer query be all upgrades. What do you think?

I got IT!!!

SELECT
  dbo.ProjectUpgrades.projectUpgradeID,
  dbo.ProjectUpgrades.projectUpgradeName,
  upID.projectUpgradeID
FROM
  dbo.ProjectUpgrades
  left outer join(
 SELECT
  U.projectUpgradeID
FROM
  projectUpgrades U
  LEFT OUTER JOIN rel_Projects_ProjectUpgrades R ON (U.projectUpgradeID = R.projectUpgradeID)
WHERE
  ProjectID = 1
GROUP BY
  U.projectUpgradeID) upID on upID.projectUpgradeID = ProjectUpgrades.projectUpgradeID

THanks to all, especially to calpunia for the help!

The Group By was the key!