Solved

Return all items and indicate if a relationship exists?

Posted on 2008-10-10
13
258 Views
Last Modified: 2010-05-18
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
Comment
Question by:ddivita
  • 7
  • 4
  • 2
13 Comments
 
LVL 6

Expert Comment

by:Jammer59
ID: 22688870
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
 
LVL 6

Expert Comment

by:Jammer59
ID: 22688910
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
 
LVL 10

Expert Comment

by:calpurnia
ID: 22688951
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
 

Author Comment

by:ddivita
ID: 22689505
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
 
LVL 6

Assisted Solution

by:Jammer59
Jammer59 earned 200 total points
ID: 22689637
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
 

Author Comment

by:ddivita
ID: 22689876
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 6

Expert Comment

by:Jammer59
ID: 22690221


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
 

Author Comment

by:ddivita
ID: 22690806
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
 

Author Comment

by:ddivita
ID: 22691006
calpurnia is on the right track. I just need to be able to only show for 1 ProjectID now.

Daniel
0
 
LVL 10

Accepted Solution

by:
calpurnia earned 300 total points
ID: 22691553
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
 

Author Comment

by:ddivita
ID: 22691677
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
 

Author Comment

by:ddivita
ID: 22693859
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
 

Author Closing Comment

by:ddivita
ID: 31505088
The Group By was the key!
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now