Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 271
  • Last Modified:

Return all items and indicate if a relationship exists?

I have three tables: Upgrades, Projects, and a relationship table that acts as relationship table(many to many) we will call it rel_Projects_ProjectUpgrades

In the rel_Projects_ProjectUpgrades table i have the primary keys for the Upgrades and Projects table. One project may have several upgrades. How can I write the SQL to show me all the upgrades available and indicate which ones are associated with a project? I have been racking my brain on this. Thank you

Here is what I tried:
SELECT *
FROM
  dbo.Projects
  RIGHT OUTER JOIN dbo.rel_Projects_ProjectUpgrades ON (dbo.Projects.projectID = dbo.rel_Projects_ProjectUpgrades.projectID)
  RIGHT OUTER JOIN dbo.ProjectUpgrades ON (dbo.rel_Projects_ProjectUpgrades.projectUpgradeID = dbo.ProjectUpgrades.projectUpgradeID)

I don't get anything back. I tried goign the other way (selecing from Projectupgrades) using a left join
0
ddivita
Asked:
ddivita
  • 7
  • 4
  • 2
2 Solutions
 
Jammer59Commented:
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
0
 
Jammer59Commented:
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  
0
 
calpurniaCommented:
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;
0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
ddivitaAuthor Commented:
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
0
 
Jammer59Commented:
Can you post the output from the two queries?  
I did something similar with the Northwind database and had some null entries on the 2nd query which should have shown:
(1) Projects and associated upgrades
(2) Projects without upgrades
(3) Upgrades without projects
 
0
 
ddivitaAuthor Commented:
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

Open in new window

0
 
Jammer59Commented:


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 

Open in new window

0
 
ddivitaAuthor Commented:
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

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

Daniel
0
 
calpurniaCommented:
Do you mean something like:

SELECT U.UpgradeID, Count(R.ProjectID) AS CountOfProjectID
FROM Upgrades AS U LEFT JOIN Rel_UpgradesProjects AS R ON U.UpgradeID = R.UpgradeID
WHERE ProjectID = 2
GROUP BY U.UpgradeID;
0
 
ddivitaAuthor Commented:
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?
0
 
ddivitaAuthor Commented:
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!
0
 
ddivitaAuthor Commented:
The Group By was the key!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 7
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now