CODEWITHSUNDEEP
sqljoin
BGeorge
BGeorge

Reputation: 139

Avoid repetition in SQL Query

I have three tables.

Project

Project ID | Project Name
         1 | Project 1
         2 | Project 2

Enhancement Pjt

Enh ID | Enh Name   | Project ID
    1  | Enh Name 1 | 1
    2  | Enh Name 2 | 1
    3  | Enh Name 3 | 2

Support Table

Supp. ID | Supp. Proj Name | Project ID
       1 | Supp Name 1     | 1
       2 | Supp Name 2     | 2
       3 | Supp Name 3     | 2

I want to create a query table which lists all the Projects and their associated Enh. Projects and Support Projects. I used joins but the result contains repeating columns in the support table which is quite confusing for a report.

Upvotes: 2

Views: 3102

Answers (3)

René Nyffenegger
René Nyffenegger

Reputation: 40499

I believe you want to use union all. This could be a start:

select 
  'Enhancement: ' type_,
   p.name,
   e.name
from 
   project p,
   enhancement e
where
   p.id = 
   e.project_id
      UNION ALL
select 
  'Support: ' type_,
   p.name,
   s.name
from 
   project p,
   support s
where
   p.id = 
   s.project_id;

Background for my proposal:

I believe what the original posters wants to avoid is the following repetition

select
  p.name,
  e.name,
  s.name
from
  project     p,
  enhancement e,
  support     s
where
  p.id = e.project_id and
  p.id = s.project_id ;

results in 

NAME            NAME            NAME
--------------- --------------- ---------------
Project one     Enhancement 2   Support 1
Project one     Enhancement 1   Support 1
Project two     Enhancement 3   Support 2
Project two     Enhancement 3   Support 3

That is: Enhancement 3 and Support 1 is returned twice, which is (probably) not wanted.

However, with my proposal, the query returns

TYPE_         NAME            NAME
------------- --------------- ---------------
enhancement:  Project one     Enhancement 1
enhancement:  Project one     Enhancement 2
enhancement:  Project two     Enhancement 3
support:      Project one     Support 1
support:      Project two     Support 2
support:      Project two     Support 3

that is, each enhancement and support case is returned exactly once.

I have chosen UNION ALL instead UNION so that the RDBMS does not have to do the extra step of filtering out duplicate records (which I believe is not necessary).

Upvotes: 2

Ben English
Ben English

Reputation: 3918

andIt would help if you could explain the relationships of your tables. It appears to me as though each Project may have a related Enhancement Project and/or a related Support Project. If I am understanding you correctly then the below will work.

    SELECT
    p.ProjectID,
    p.ProjectName,
    e.EnhName,
    s.SuppProjName
    FROM
    Project p 
    LEFT JOIN EnhanchementPJT e ON p.ProjectID = e.ProjectID
    LEFT JOIN SupportTable s ON p.ProjectID = s.ProjectID

Upvotes: 0

Jens Schauder
Jens Schauder

Reputation: 81862

I think you are looking for aliases

try something like this:

select p.name as project_name1, // the 'as' might be superfluous ... not sure right now
       e.name as enhancement_name1
from project as p, enhancement as e // whataver the desired jon condition is
where p.id = e.project_id

Upvotes: 0

Related Questions