Solved

Return all items and indicate if a relationship exists?

Posted on 2008-10-10
13
263 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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
 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

733 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