daily pastebin goal
61%
SHARE
TWEET

Untitled

a guest Aug 10th, 2018 53 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Avoid repetition in SQL Query
  2. Project ID | Project Name
  3.          1 | Project 1
  4.          2 | Project 2
  5.    
  6. Enh ID | Enh Name   | Project ID
  7.     1  | Enh Name 1 | 1
  8.     2  | Enh Name 2 | 1
  9.     3  | Enh Name 3 | 2
  10.    
  11. Supp. ID | Supp. Proj Name | Project ID
  12.        1 | Supp Name 1     | 1
  13.        2 | Supp Name 2     | 2
  14.        3 | Supp Name 3     | 2
  15.    
  16. select
  17.   'Enhancement: ' type_,
  18.    p.name,
  19.    e.name
  20. from
  21.    project p,
  22.    enhancement e
  23. where
  24.    p.id =
  25.    e.project_id
  26.       UNION ALL
  27. select
  28.   'Support: ' type_,
  29.    p.name,
  30.    s.name
  31. from
  32.    project p,
  33.    support s
  34. where
  35.    p.id =
  36.    s.project_id;
  37.    
  38. select
  39.   p.name,
  40.   e.name,
  41.   s.name
  42. from
  43.   project     p,
  44.   enhancement e,
  45.   support     s
  46. where
  47.   p.id = e.project_id and
  48.   p.id = s.project_id ;
  49.    
  50. NAME            NAME            NAME
  51. --------------- --------------- ---------------
  52. Project one     Enhancement 2   Support 1
  53. Project one     Enhancement 1   Support 1
  54. Project two     Enhancement 3   Support 2
  55. Project two     Enhancement 3   Support 3
  56.    
  57. TYPE_         NAME            NAME
  58. ------------- --------------- ---------------
  59. enhancement:  Project one     Enhancement 1
  60. enhancement:  Project one     Enhancement 2
  61. enhancement:  Project two     Enhancement 3
  62. support:      Project one     Support 1
  63. support:      Project two     Support 2
  64. support:      Project two     Support 3
  65.    
  66. select p.name as project_name1, // the 'as' might be superfluous ... not sure right now
  67.        e.name as enhancement_name1
  68. from project as p, enhancement as e // whataver the desired jon condition is
  69. where p.id = e.project_id
  70.    
  71. SELECT
  72.     p.ProjectID,
  73.     p.ProjectName,
  74.     e.EnhName,
  75.     s.SuppProjName
  76.     FROM
  77.     Project p
  78.     LEFT JOIN EnhanchementPJT e ON p.ProjectID = e.ProjectID
  79.     LEFT JOIN SupportTable s ON p.ProjectID = s.ProjectID
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top